Excel Project

Today, I completed an excel analysis project that addressed the effectiveness of a particular workout. The data was broken out by age and gender, and the goal was to see if the heart rate of these people ever hit the target heart rate. To do this type of analysis, I needed some data such as max heart rate, target heart rate, target achieved, and the percentage increase. The problem was, I only had the raw data so I had to calculate the data. To get the max heart rate, all I had to do was enter in the cell =max(startingcell: endingcell) and I copied that formula for all rows. To get target heart rate, I just had to enter the target percentage on the worksheet and then come up with a formula that addressed that cell absolutely. So under the target heart column I entered in my calculation as = $E$1*D3. Also, I learned to make a cell reference absolute instead of relative, all you have to do is hit the F4 in the formula right before the cell. This inserts the $ signs automatically. I copied this cell for the rest of the entries to get the target heart rate of each individual. All that was left was to come up with a check to see if the max heart rate was greater than or equal to the target heart rate. I learned that this statement (=IF(F3>=E3,"Yes","No")). This was copied to the rest of the cells. To get average increase, I just made the formula =(F3-I3)/I3 where F3 is the max column, and I3 is the starting column, and then I just copied that formula for the rest of the entries.

Now we have the data that we want such as target heart rate, max heart rate, and whether or not the workout was effective. However, it was only for each person and there was no aggregations being done with the data. I learned that a pivot table will provide all of the different aggregations for this type of data. It was as simple as clicking on the Insert tab and selecting Pivot table. It defaulted to the correct range of data, and I hit ok. It now was in a new worksheet, and all I had to do was drag and drop the fields I wanted to either columns, rows, or aggregation. Since we wanted it broken down by gender, that can be inserted into the rows, the target achieved field could be dragged into the column portion, and the id column can be dragged to the aggregate block since we want a count of people. The end result of the pivot table options should look like the following:

Please note that you have to click on the aggregate field in the pivot table options and click value field settings. This needs to be changed to a count instead of a sum. Once this is performed, we now get a table that shows the number of people broken down by gender of whether or not they achieved their target heart rate.

The final result of the analysis is displayed here:

The last piece of analysis was to create another pivot table which was done the same way as before. Only, the pivot table options should look like this instead:

 Now, on the pivot table itself, we want the data grouped by age by decade, so just right click the age column, click group, and put in the number 10 to break it down into decades. I also learned to get rid of the grand total column, you just have to right click the pivot table, click on pivot table options, select the totals and filters options, and then just uncheck the grand totals boxes.

The end result looks like this:











Overall, I now feel a lot more comfortable working with pivot tables, and I learned that in some cases it does not matter if you put the fields you want to group by in either the columns tab or the row tab. I also learned that in the page layout options, you can put in a header or footer there instead of having to put the header on the actual worksheet and specifying that particular row to repeat every page. This project has definitely boosted my business analysis capabilities.

Sources for pictures:

Richard Ecker via Snipping tool