Excel Project

I just completed an analysis of heart rate measurements taken by my boss for 500 individuals during a particular workout at the fitness center. Heart rates were taken every minute starting at zero and ending at 20 minutes. My boss is interested in the heart rates of a variety of active individuals both male and female ages 20-49 before, during a 15 minute workout, and during a 5 minute cool down.  She was most interested in whether or not the subject reached their target heart rate for their age, as well as the average percent increase in heart rate from start to highest heart rate recorded. in order to find this information, I had to use a number of formulas that connected cells to each other to form easily readable data for my boss. I referred to the Help Button (upper right hand corner in Excel 2007) numerous times for reference.

First I had to find out which subjects reached their target heart rates for their age. My boss gave me a target heart rate of 80% which, if changed, would affect the numbers in the cells that were connected to it. The target heart rate would be 80% of the maximum HR. Since the Maximum HR for an individual is roughly 220-Age, I used the formula "=220-[subject Age Cell]" in order to get the Maximum HR, then I used the formula "=[MaxHR]*$E$1" where cell E1 is currently 80% in order to lock in that cell to find the TgtHR. Finally, I used the IF function to return a "Yes" if the HighestHR recorded was >=TgtHR, or return a "No" if the HighestHR was NOT >=Tgt HR. I created a Pivot table to refine the data by Females and Males that returned a Yes or No. Grand Totals are also displayed to show the bigger picture to my boss.

Then, she wanted to know the average percent increase in HR. I had to use the MAX function to find the highest HR recorded for each individual. We then used the formula "=(HighestHR-resting HR)/resting HR" to determine the percent increase that was achieved at the highest heart rate compared to the heart rate recorded at time 0:00. I then transferred the data to another worksheet creating a Pivot table more specifically grouping the data by Sex and by Age Group. The breakdown by demographic group will allow my boss to more accurately determine which groups benefit the most from these exercises.

Pictured below, I took a screen shot of the Pivot Table showing the Average Percent Increase in HR. *Notice that I suppressed the Grand Totals column and row that automatically show to leave only the information by demographic for my Boss.

This project allowed me to better understand the functions that I learned about Excel. I practiced manipulating and grouping data using different linkages throughout an excel workbook. I also took the time to learn about page layout and how to print a large group of data with many rows and columns and got practice with using tables to show only data that is most useful to the recipient. This project could easily transfer to a real world scenario and I will know how to use my knowledge of Excel to work through it, even if that means half of the time in the Help window.