C/IL 102   Spring 2009
HW #1: Golf Scores Worksheet
Due: 5pm, Wednesday, April 8, 2009

Click here to download a Microsoft Excel worksheet that serves as the basis of this assignment.

This worksheet is a record of Joe Golfer's golf performances on the first nine holes at the Dutch Elm Disease Country Club during 2005-2008.

The top row of the worksheet lists the par of each of the nine holes. (The par of a golf hole is the number of strokes that an expert golfer would be expected to take on the hole, under normal conditions.) Beneath that is a row containing the hole numbers, which act as column headings for the rows below.

Each row in the main body of the worksheet is a record of one of Joe's 9-hole rounds of golf: The cell in the first column contains the date on which he played and the remaining cells indicate how many strokes he took on each of the nine holes.

You are to complete the worksheet by doing the following:

  1. Insert a new row 1, and in column A of that row place your name and your class section's meeting time (e.g., MW 1pm, MW 3pm).

  2. For each round of golf, calculate the total score (the total number of strokes taken on all nine holes) in a cell in the corresponding row, in the column immediately to the right of the scores for the 9th hole. (Insert the label Total at the top of the column.)   Use the appropriate function ---namely, SUM--- to perform this calculation. An invocation of this function has the form

    SUM(<range>)

    For example, SUM(B5:F8) yields the sum of the values in the rectangular region with corners at cells B5 and F8. (Such a region is referred to as a range of cells.) (Don't forget to begin each formula with an equals sign (i.e., "=").)

  3. At the bottom of the column corresponding to the 1st hole, use a cell to calculate the average number of strokes taken by Joe on that hole. In the two cells immediately below that one, calculate the minimum and maximum scores recorded by Joe on that hole. Use the appropriate functions, which should be easy to find using the Insert Function utility in Excel. (It looks like the symbol  fx  and is (at least in my version of Excel) immediately to the left of the text box that shows the contents of the current cell.) All three functions are invoked (like SUM) using an expression of the form

    function-name(<range>)

    Do the same for holes 2 through 9, as well as for the 9-hole total scores. (Of course, you should be able to accomplish this by simply copying each formula for hole 1 into the nine cells to its right.) Place an appropriate label in the leftmost cell of each of the three rows of data that you just created.

  4. In the leftmost column of the next several rows (you may want to leave a row or two empty), respectively, enter the labels Eagle, Birdie, Par, Bogey, Double Bogey, and Triple Bogey. You may abbreviate the last two (e.g., "D. Bogey"). (These terms refer to playing a hole of golf in a number of strokes that is two fewer than, one fewer than, equal to, one greater than, two greater than, and three greater than, respectively, the par for that hole. For example, completing a par 5 hole using only 4 strokes is a birdie; completing a par 4 hole using 7 strokes is a triple bogey.)

    Find the cell at which the row where you just put the label Eagle intersects the column corresponding to the 1st hole. In that cell, place a formula to calculate the number of eagles Joe made on the 1st hole. (See hint below.) Use the cells below it to calculate the number of pars, bogeys, etc., that Joe made on the 1st hole.

    If you designed the eagle-counting formula well (using dollar signs to specify absolute addressing in the right places), you should be able to copy it into the cells below and then just make a minor modification to each of them in order to count birdies, pars, etc. (Indeed, if your eagle-counting formula makes judicious use of the column of cells containing -2, -1, ..., +3 that you will find at the bottom of Column A, you should be able to copy it to the cells beneath it without making any manual changes thereafter!)

    Then do the same for holes 2 through 9.   (If your formulas for the 1st hole were designed in the proper way, you should be able to copy them into the columns corresponding to the remaining holes without further modification.)

    The appropriate function to use for counting eagles, birdies, pars, etc., is CountIF. An invocation of this function has the form

    CountIF(<range>, <criterion>)

    The value it yields is the number of cells in the specified range that are equal to the value indicated by the criterion. For example, the value of CountIF(D3:E40, 4) is the number of cells in the range D3:E40 containing the value 4. Similarly, the value of CountIF(D3:E40, B2) is the number of cells in the range D3:E40 containing the same value as that contained in cell B2. One can even write CountIF(D3:E40, B2+1), which yields the number of cells (in the specified range) containing a value equal to one more than that contained in cell B2.

    Note: For a small deduction in points, you may calculate the number of times that Joe scored each of 2, 3, 4, ..., 8, on each hole, rather than the number of eagles, birdies, pars, etc., that he scored. (If you choose this option, use 2, 3, ..., 8 as labels rather than Eagle, Birdie, Par, etc.)   This option makes things a little simpler, due to the fact that you no longer need to take account of the fact that different holes have different pars. End of note.

  5. Using the values calculated in the previous step, calculate the total number of eagles, birdies, pars, bogeys, etc. (or 2's, 3's, 4's, etc., if you followed that option), that Joe scored during his play. Use the cells in the obvious rows (namely, the ones labeled Eagle, etc.) and the column corresponding to the 9-hole total scores.

  6. Embed in your worksheet a pie chart to illustrate the relative frequencies with which Joe made eagles, birdies, pars, bogeys, etc., during his play. (The relevant numbers were calculated in the previous step.) Specifically, the pie chart should include one slice for each of the categories Eagle, Birdie, Par, ..., Triple Bogey, and each slice's size should be proportional to the frequency with which Joe made the corresponding score. If Joe made twice as many bogeys as birdies during the year, for example, the slice corresponding to Bogey should be twice as large as the slice corresponding to Birdie.

    Note: Even if you could not complete steps (4) and (5) correctly, you can still attempt (6) (this step). Specifically, calculate (manually) the number of eagles, birdies, pars, etc., that Joe scored on the 1st hole. Enter these numbers into the cells where you were to have placed formulas to calculate them. Now construct a pie chart that illustrates the frequencies with which Joe scored eagles, birdies, pars, etc., on the 1st hole. End of note.

    Extra Credit: Label each pie slice by the term to which it corresponds. (To accomplish this, use as Category Labels the range of cells in which you placed the words Eagle, Birdie, Par, etc.

  7. Change the scorecard dated May 8, 2006 so that the date is May 7.

  8. Reorder the rows of the worksheet so that they are in ascending order with respect to the column containing the 9-hole (total) scores.

  9. E-mail your completed worksheet file to your instructor at mccloskeyr1@scranton.edu.