Excel Project

For the Excel Project, we were given a set of data and asked to format and manipulate it in various ways to learn the different uses and functions of Excel. First, we did some minor formatting, such as bolding text and centering titles, as well as adding some columns. Next, we utilized the "Freeze Pane" feature in Excel to freeze the first two rows, which contain all of the column titles and headings. We then added a column for Maximum Heart Rate, and entered a formula to find the max heart rate, =220-Age, for each person and copied it down all to cover the entire column of applicable cells. For age, we simply referenced the cell that corresponds to each person's age so we didn't have to input data for each cell. Again, we inserted another column, this time to find each person's Target Heart Rate. We did this by creating a cell with a designated target heart rate for each person of 80% of their max heart rate, which we used as an absolute reference, and multiplied it by each individual's max heart rate found earlier, and used this as a relative reference.

Next, we had to find the Highest Heart Rate Achieved by each subject. To do this, we used the "=LARGE" function in Excel and found the largest number, or highest heart rate in this case for each subject, from their field of data. Continuing along, we then had to formulate a test to see if each subject achieved their target heart rate. To do this, we used the "=IF" function in Excel to compare each subject's target heart rate with their max heart rate. If the max was the same or greater than the target, the test returned a "Yes". If the max was less than the target, the test returned a "No". For the final data portion of the worksheet we had to come up with a percentage increase in heart rate for each subject. To do this, we found the difference between each subject's highest heart rate and starting heart rate, and divided this difference by the starting heart rate. The next few things we did were formatting, such as adjusting each column to the appropriate with, changing the page orientation and page size for printing, adding a header, etc.

Now that we have all of data inputted, formatted, and saved, we then had to create a couple Pivot Tables in Excel. This was the part I personally found to be the most interesting as I had not used Pivot Tables before. For the first Pivot Table, we had to sum up the number of both male and female subjects, as well as a grand total, who reached and did not reach their target heart rate during the 15 minute exercise window. After doing this Pivot Table, I realized they were pretty straight forward to do. It's simply a matter of selecting the data you want to include, with headings, and then sorting it based on specifications. The last Pivot Table we did was a breakdown of Average Percentage increase for both male and female subjects, in the age groups of 20 to 29, 30 to 39, and 40 to 49. To do this, we selected the data and headings, selected that we wanted to make a Pivot Table on another sheet, and then sorted the data per specifications to attain the proper read out. Below is a screen capture from my second Pivot Table that shows the Average Increase Data per the age groups specified above.

That's all for the Excel Project Post!