Excel Project

To give you, my dear reader, a feeling about the Excel assignment, I first like to start with a short introduction to the problem, which has to be solved. This is followed by a paragraph dealing with data formatting tasks. The last paragraph introduces you to data analysis by using Pivot tables, a very useful Excel tool.

Introduction:

A fitness center is designing a new workout plan for their customers and is doing some research about the effectiveness, by testing the workout in practice. After data of 500 people participating was collected, fitness center management needs some help in preparing, formatting and analyzing the newly gathered data to determine if the workout is effective and should be implemented, i.e. offered to their customers.

Formatting the raw data:

A lot of data was already given, as the age and the gender of the 500 participants. Furthermore the subject’s heart rate was recorded in a one minute interval, whereby the workout lasts for 15 minutes, followed by a 5 minutes cool-down period.

According to the given data it is not possible for the decision makers to draw any conclusion about the workout program. 

Therefore, some data adjustment/manipulation is needed. Following the assignment I created some new columns and used Excel formulas to calculate the values of interest:

• Column D: MaxHR stands for the maximal heart allowed and it is estimated to be 220 less the age. Therefore the formula for cell D3 is: =220-B3
• Column E: TgtHR is the target heart the workout wants to achieve and is estimated to be 80% of the MaxHR. It is a measure of success and the formula for cell E3 is: =D3*$E$1. In cell E1, the percentage (80%) is given and you have to make it an absolute value by the dollar signs. It is very useful to have the percentage in a separate cell, since you can easily adjust the whole data by just changing cell E1, for instance from 80% to 90%.
• Column F: HighestHR stands for the highest heart rate a participant achieved during the training. You can easily find this value using the following formula for the cell F3: =MAX(I3:AC3)
• Column F: TgtAchieved shows, if the target heart rate is achieved. You can use the “If-formula” and for the cell F3 it looks like: =IF(OR(F3=E3;F3>E3);"yes";"no"). If the target heart rate is achieved (higher or equal the max. heart rate, then there should be a “yes”, otherwise a “no”.
• Column G: PctIncreaseHR stands for the percentage increase in the heart rate from the lowest to the highest heart rate value. The formula for cell G3 is: =(F3-I3)/I 

Now we have data, which can be used to interpret the usefulness and effectiveness of the new workout. Still data is not very clear. Therefore the next paragraph shows you how to fix the issue of making data clearer and valuable through data analysis

Data analysis:

You can use the Pivot-table function of Excel to summarize and combine data. A pivot table is a great reporting tool that sorts and sums (raw) data into a clear layout in a new worksheet. For instance if the manager wants to know for which demographic group the workout was the most beneficial you can use such Pivot-tables to analyze. The following screenshot shows such a Pivot table.

 

  

But how to create such a Pivot Table?

1. Mark all date that is of interest for your Pivot-table 
2.   Go to the Insert tab and select Pivot-table to your left 
1. Select target data 
2. Choose where you want the Pivot table to be placed (new worksheet) 
3.  The new work sheet will open and you can now use the Pivot-table field list to arrange and sort data appropriately.

1. Just by choosing fields and dragging them to particular fields like “Row Labels” or Column Labels” you can create a useful data matrix 
2. In the field “values” you have to drag the variable of interest, which was “PctIncreaseHR” in my case.
4.   The output can be adjusted 
1.   Group items, for instance I grouped Subject Age into decades, i.e. Group 1 is from age 20 to  29, Group 2 is from 30-39 and so on. 
2.    Delete Grand Total rows/colums 
3.    Summarize values as “sum”, “counts” or “averages”, etc 
4.   Format cells e.g. turn numbers into percentages or change decimals