Golf

Posted by Darren Smith on May 07, 2001 1:32 PM

I am trying to make a spreadsheet for my father that will calculate his golf handicap. The problem I have is that I have a column of golf scores, and I want to be able to put in a new score each week but only calculate the last five scores including the new one. Any ideas??

Posted by IML on May 07, 2001 2:35 PM

Darren,
If your dad is going to be continually adding his scores, could you just put a formula in that just captures the last 5. I don't know if you need the average or sum or what, but if your the scores are being entered in say, column B, you could enter the formula =sum(B1..b5) in column C and copy it down. If you don't want a bunch numbers making your sheet look messy, you could enter
=IF(B5="","",SUM(B1:B5))

Sorry if I'm not understanding completely.

Posted by Darren Smith on May 07, 2001 3:10 PM

But I will be adding a new number all the time and I only want the calculation to be performed on the last five numbers. Like say I have a column of numbers A1-A10 I then have a calculation in say B1 of SUM(A6:A10)*2 When I put a new number in A11 I want the calculation in B1 to be SUM(A7:A11)*2 how do I get it to calculate the last five numbers automatically without having to redo the formula each time. Does this make any sense?
Thanks

Posted by IML on May 07, 2001 3:23 PM

Here is what I was thinking:
In column A, it is the date of the round.
In column B, it is the score of the round.
In column C, you use to calculate the current handicap. This way you can see a running total.

Lets say in row 2 you put the titles, Date, Score, Handicap.
Once you have five scores, in cell C7 put the handicap formula as follows
=IF(B7&LT;&GT;"",SUM(B3:B7)*2,"")
Now copy this done as far as you need to go. It will display nothing until a score is entered next to it.

If you would like to diplay his current handicap at the top you could put the following formula that would pickup the handicap next to the last round entered.

=INDEX(A3:C999,MATCH(MAX(A3:A999),A3:A999,1),3)

Posted by Aladin Akyurek on May 07, 2001 3:44 PM

Darren,

I don't know much about golf, sigh.
I'll assume the following data that occupy the range A1:B10.

{"WEEK","SCORE";1,3;2,4;3,2;4,7;5,2;6,6;7,8;8,4;9,5}

That is, you have values in A that date golf scores in B.

In C2 enter: 5 [ number of last values that you want to use in calculations ]

In C3 enter: =COUNT(A:A)+1

If you don't have a WEEK column (column A in my example data), then:

In C3 enter: =MATCH(E1+25,B:B) [ I owe this to Dave Hawley ]

In C4 enter: =MIN(C2:C3)

In C5 enter: =ADDRESS(ROW(B2),COLUMN(B2))&":"&ADDRESS(C3,COLUMN(B2))

In D2 array-enter: =SUM(SUM(INDIRECT(ADDRESS(LARGE(ISNUMBER(INDIRECT(C5))*ROW(INDIRECT(C5)),ROW(INDIRECT("1:"&C4))),COLUMN(INDIRECT(C5)))))/C4)

Note. To array-enter a formula, you need to hit CONTROL+SHIFT+ENTER at the same time instead of just ENTER.

This formula calculates an average of 5 last golf scores you entered.

In D3 array-enter: =SUM(SUM(INDIRECT(ADDRESS(LARGE(ISNUMBER(INDIRECT(C5))*ROW(INDIRECT(C5)),ROW(INDIRECT("1:"&C4))),COLUMN(INDIRECT(C5))))))

This formula sums 5 last golf scores you entered.

You can sort out yourself other type of calculations that you might need yourself by using the array formulas as examples.

Note. If your dad skips en entry, that won't matter. The above formulas will still use last 5 numeric entries.

Note. I'm sending you the workbook that shows how the above system of formulas work.

Aladin

==============