Duncan,
The rules of the Callaway system is that the highest score that can count against the golfer is
twice the hole's par. So, if you shoot 10 on a par 4, your score is 8 for that hole - thus the
formula. So, in your example, on the 15th hole, the 7 on a par 3 only counts as 6.
See
http://golf.about.com/od/handicaps/l/blcallawaysys.htm
for the scoring system that I implemented.
As for the raw score, I made a mistake, forgetting to use absolute references. Instead of :
=SUM(IF(C3:C202*B3:B20,2*B3:B20,C3:C20))
I should have used
=SUM(IF(C3:C202*$B$3:$B$20,2*$B$3:$B$20,C3:C20))
entered with Ctrl-Shift-Enter. Then it will work correctly when copied to the right.
Note that I didn't make that mistake in the function usage. This is correct, and can be copied.
=Callaway($B$3:$B$20,C3:C20,$B$1)
Sorry about that...
Bernie
MS Excel MVP
I am extremely grateful to you. I have noticed one small problem
though, and I'd appreciate your input on this.
I have the following par's enterered in column B - 5, 4, 4, 5, 3, 5,
4, 3, 4, 4, 3, 4, 5, 4, 3, 4, 5, 3
Along with the following scores in column C - 6, 6, 6, 10, 5, 7, 5, 4,
5, 7, 3, 6, 7, 6, 7, 6, 8, 4
These scores total, as far as I can count, 108. However, the
spreadsheet you've provided, totals it as 107.
If I copy and paste the entire column into the next clear column in
the spreadsheet, it does indeed total 108.
Also, if I can ask about the formulas...
In the "Raw Score" row, you have the following formula in C21:
{=SUM(IF(C3:C202*B3:B20,2*B3:B20,C3:C20))}
I'm guessing, from reading the formula, that this is saying:
If the Sum of the values in the range C3:C20 (Golfer One) is greater
than the sum of double the values in the range B3:B20 (hole par's),
then the cell C21 gets the value of double B3:B20 (the hole par's)
else, it get the value of the sum of C3:C20 (the hole scores). Is
this right?
Where I am getting confused is with the formula in cell D21.
In D21, you have the formula:
{=SUM(IF(D3:D202*C3:C20,2*C3:C20,D3:D20))}
From this, I believe it is saying...
The Sum of the values in the range D3:D20 (Golfer Two) is greater than
the sum of double the values in the range C3:C20 (Golfer One), then
the cell D21 gets the value of double C3:C20 (Golfer One scores) else,
it get the value of the sum of D3:D20 (Golfer Two scores). Is this
right?
Many thanks for your help
Duncan