The purpose of this assignment is to introduce you to basic functions of Microsoft Excel by building a spreadsheet for monitoring workout heart rates. This is an individual assignment! You may receive help from your classmates but must build the spreadsheet yourself.

The effectiveness of an aerobic training program can be measured by heart rate during training using the Karvonen formula. According to the Karvonen formula, a person's aerobic workout should be designed to maintain a heart rate within a Training Zone.

• AGE is a person's age in years.
• RHR (Resting Heart Rate) is measured by the person's pulse at rest, in beats per minute.
• MHR (Maximum Heart Rate) is calculated by:  220 - AGE
• The low end of the Training Zone is calculated by:   (MHR - RHR) * 0.50 + RHR
• The high end of the Training Zone is calculated by:   (MHR - RHR) * 0.85 + RHR
• Note that the "*" operator is multiplication.

Everything is rounded to the nearest whole number.

You will transform a spreadsheet containing heart rate data into a useful tool for tracking the effectiveness of an aerobic training program custom designed for the Miller family and based on the Karvonen formula.

Steven Miller is 49 years old and has a resting heart rate of 74 beats per minute.
His daughter Diane Miller is 22 years old and has a resting heart rate of 58 beats per minute.

I have provided a "raw" spreadsheet called rawdata.xls with two named worksheets, one for each member of the Miller family. Each worksheet contains heart rate measurements taken during four workouts.

• Rows 1 through 5 of each worksheet represent the elapsed time since the start of the workout: 10, 20, 30, 40 and 50 minutes.
• Columns A, B, C, and D of each worksheet contains the five heart rate measurements for the Tuesday, Thursday, Saturday, and Sunday workouts, respectively.

You must transform it, following the specifications below, into a spreadsheet called heartrate.xls with two worksheets that resemble Figure 1 and one that resembles Figure 2.

Important note:  A spreadsheet which looks like this can be generated by simply typing numbers and letters into the appropriate cells!  I will not give credit for such a solution.  Most of the cells which display numbers in fact contain formulas.   Specifications for building the spreadsheet are given below.

Important note: The images below contain borders around certain cell boundaries. Your spreadsheet needs to include the same borders. Borders may be hard to see in the images (they are black lines, versus gray) so if you have any doubts, ask!

Detailed Specifications of Your Assignment
(point values shown in parentheses)

1.  Start by downloading the Excel file rawdata.xls (browse to this web page, right-click on the file name and select "Save Target As..." -- Firefox says Save Link As).  Save it in your home folder.

2.  Open rawdata.xls using Excel.

3.  On the File menu, select Properties then type in "CSC 100 Project 2" (without the quotes) as the Title, "Miller Workout Results" (without the quotes) as the Subject, and your own name as Author. Click OK. (1 point)

4.  Save the file as heartrate.xls (1 point)

5.  Transform worksheet Steven into its Figure 1 format by following these instructions exactly:

1. Insert a blank column on the left by clicking on any cell in column A, then from the Insert menu selecting Columns. Insert another blank column on the left. Columns A and B should now both be blank. In Column B, type in the values 10, 20, 30, 40 and 50 in rows 1 through 5 to represent the minute markers. (1 point)
2. Similarly insert 3 blank rows at the top. Then add labels as shown in rows 1 and 3 of Figure 1, and labels as shown in cells B10, B11, and B12.
3. Notice that the row 1 label spans all six columns and is centered (hint: select the cells and click  ) (1 point)
4. Cell G4 will contain the average of the heart rates in columns C through F of row 4. You must enter it as a formula (hint: use the Average function). Do the same for G5, G6, G7 and G8. (1 point)
5. Cell C10 will contain the average of the five measurements immediately above it in cells C4 through C8. You must enter it as a formula. Do the same for D10, E10, F10 and G10. (1 point)
6. Cell C11 will contain the minimum of the five measurements above it in cells C4 through C8. You must enter it as a formula (hint: use Minimum function). Do the same for D11, E11 and F11. (1 point)
7. Cell G11 will contain the minimum of the four minimums C11, D11, E11 and F11. (1 point)
8. Cell C12 will contain the maximum of the five measurements above it in cells C4 through C8. You must enter it as a formula. Do the same for D12, E12 and F12. (1 point)
9. Cell G12 will contain the maximum of the four maximums in row 12. (1 point)
10. Make sure all values display as whole numbers (format cells for 0 decimal places). (1 point)
11. Draw all cell borders as shown in Figure 1 (using   feature), and make sure cell contents are centered (except B10, B11, and B12). (1 point)

6.  Transform Diane's worksheet similarly.(1 point)

7.  Build a new worksheet called Summary as shown in Figure 2.

1. Insert a new worksheet and rename it "Summary" (without the quotes), by double-clicking the name.  Make sure its tab appears to the left of the other two. (1 point)
2. Add labels into the worksheet as shown in Figure 2. In the subtitle, substitute your name for mine!
3. Fill in Columns B, C and D. Fill in the Name, Age and RHR columns as shown in Figure 2.  These are all given. Columns C and D contain the only numeric cells in this worksheet that are not formulas!
4. Fill in Columns E, F and G. Enter mathematical formulas to calculate the MHR, low end and high end of the training zone.  Remember that all formulas start with "=".  Format the cells so they are displayed as whole numbers. Except for the values 220, 0.50 and 0.85, all components of these three formulas must be cell references! Here's how to test if you've done it correctly: Change Steven's age to 55. The MHR should change to 165, the low end should change to 120 and the high end to 151. If not, you're doing it incorrectly. Don't forget to change his age back to 49. (8 points)
5. Fill in Column H. Each numeric cell in column H is a reference to the average of averages (cell G10) in the corresponding worksheet. (1 point)
6. Fill in Column I. Each numeric cell in column I is a reference to the "minimum of minimums" (cell G11) in the corresponding worksheet. (1 point)
7. Fill in Column J. Each numeric cell in column J is a reference to the "maximum of maximums" (cell G12) in the corresponding worksheet.
8. Make sure all values display as whole numbers.
9. Draw all cell borders as shown in Figure 2 and center everything except Column B. (1 point)

For up to 2 points extra credit, format columns H and J in the Summary worksheet to display their values in different colors using Excel's Conditional Formatting feature. This feature must be used correctly as specified below to get the credit.

• Columns H and J will utilize a feature called "conditional formatting".  To add "conditional formatting" to a cell, select it then choose Format, Conditional Formatting....  A dialog box like that shown in Figure 3 will appear.
• The conditional format for column H (week average) cells is this:  if the week average is below the Training Zone, display it as blue.  If within the Training Zone, display it as green.  If above the Training Zone, display it as bold red.  (hint: use the "Add >>" button to add additional conditions) (1 point)
• The conditional format for column J (week maximum) cells is this: if the week maximum is above the MHR, display it as bold red otherwise display it as green. (1 point)
• The condition boundaries must be expressed using cell references, not numeric values, to receive credit. The conditions must also be expressed correctly (for instance, "below" means less than).
• If you do this correctly, the number in cell H6 will display in blue, cells J6, J7 and H7 in green.

Figure 3.  Conditional formatting

3 Point Bonus Option

For up to 3 points extra credit, create a chart of Steven's worksheet only, and place it right on the worksheet, as illustrated here.  For full extra credit, the chart needs to look exactly as shown in Figure 4, including the data lines (1 point), the legend and title (1 point), and the axis labels (1 point). See the color version on the web to get a clearer picture of its appearance.

Email me your spreadsheet file heartrate.xls as an attachment to a message to psanderson@otterbein.edu.