It can be done, but not with just a single formula. Here's one way of
doing it that doesn't need any table lookups or VBA script, but does
need you to add an extra 40-or-so columns to your spreadsheet.
Some preliminary remarks:
1. Caveat emptor! I've tested the spreadsheet on the three examples
given on the website you posted, and it works fine for them, but that
doesn't guarantee that there aren't any lurking bugs that only pop up
in certain circumstances. Heck, I even spotted some as I was writing
this up!
2. The example Callaway table given on the website only goes up to a
handicap of six worst holes, but the tables from the table generator
on the same website go as far as seven. I inferred the algorithm that
the generator uses and built it into the spreadsheet, but the way I've
done it means that the table effectively goes on forever. I.e. it is
theoretically possible for the table to give a handicap of the 15,
16,... 1000,... worst holes. I've not tried to guard against such
circumstances, so if somebody comes in at 500 over par you're on your
own.
3. There are two versions of the Callaway Method. Some only cross off
the 17th and 18th holes, while others cross off the 16th hole too.
I've generalised this to being able to cross off as many of the last
holes as you want.
4. You said that cells C4:T4 will contain the strokes for each hole,
and that you want the Callaway handicap to go in cell U4. Are you
sure about that? Wouldn't you want the gross score in U4, the
Callaway handicap in V4, and the Net Score in W4 (like on the
screenshot you posted)? I hope you do, 'cos that's how I've done it.
5. I have made the following assumptions about the way the information
is organised on your spreadsheet:
- Cells C2:T2 contain the numbers 1 to 18 in order. (Hole numbers)
- Cells C3:T3 contain the pars for the holes.
- Cells C4:T4 contain the scores for each hole.
This is how things are organised in the example screenshot you
posted (except that everything is moved one column to the right), and
if you don't do it that way then the spreadsheet won't work.
7. If you don't like the way I've arranged things the spreadsheet,
it's easy to rearrange them, but only *after* you've typed all the
formulae in.
Now for the formulae:
Step 1: Specifying How Many of the Last Holes to Cross Off
You need to reserve a cell somewhere in your workbook where you enter
how many of the last holes are crossed off (see remark 3). Choose a
cell and put the number 2 or 3 into it, depending on your preference,
and then give the cell the name "croff". (To find out how to name a
cell, press F1 and ask Clippy how to "Name cells in a worksheet".) If
you later find that you want to move the cell, highlight it and click
and drag the border to a new location. The name will now refer to
that new location, and the contents will move with the name.
(Alternatively, if you're absolutely sure that the number in that cell
will never change, then you can just replace the name croff with the
number of holes that you want crossed off in the formulae in Steps 5
and 6.)
Step 2: Calculating Gross Score and Par For The Course
In cell U3 enter the formula "=SUM(C3:T3)". Copy U3 and paste into U4.
Step 3: Double-Par Adjusting the Scores
In cell X4 enter the formula "=IF(C4>2*C$3,2*C$3,C4)". Then flood
fill from X4 into the entire region X4:AO4. These are the double-par
adjusted scores for each hole.
Calculate the adjusted gross score by entering the formula
"=SUM(X4:AO4)" in cell AP4.
Step 4: Calculating the Entry in the Callaway Table
In cell AQ4 enter the formula
"=IF(AP4<=U$3,0,ROUNDDOWN((AP4+6-U$3)/5,0)/2)". This formula uses the
course par (in cell U3) and the gross adjusted score (in cell AP4) to
calculates how many holes handicap the Callaway table would give.
In cell AR4 enter the formula "=IF(AP4>U$3,MOD(AP4+
IF(AP4>U$3+3,1,4)-U$3,5)-2,0)". This formula calculates the handicap
adjustment the Callaway table would give. (Except for in the special
case of the gross adjusted score being par or less. In the special
case no handicap is allowed regardless of what the Callaway table
says, and the formula handles this by always returning zero.)
Step 5: Crossing Out the Last "croff" Holes, and Sorting The Rest
In cell AS4 enter the formula
"=LARGE(INDIRECT(CONCATENATE(ADDRESS(ROW($X4),COLUMN($X4)),":",ADDRESS(ROW($X4),COLUMN($X4)+17-croff))),C$2)".
Then flood fill from AS4 into the entire region AS4:BJ4.
The error value "#NUM!" will appear in the last few cells that you
flood-fill. This is not an error! These represent the last few holes
that have been crossed off the score card. (There should be as many
"#NUM!"s as the number in the cell croff.)
Ignoring the error values, this formula generates a list of all the
adjusted scores that have not been crossed off the card in descending
order.
Step 6: Calculating the Handicap
You now have all that you need to calculate the handicap. Go back to
cell V4 and enter the formula "=IF(ROUNDDOWN(AQ4,0) =
0,0,SUM(INDIRECT(CONCATENATE(ADDRESS(ROW(AS4),COLUMN(AS4)),":",ADDRESS(ROW(AS4),COLUMN(AS4)
- 1 + ROUNDDOWN(AQ4,0))))))+IF(ROUNDDOWN(AQ4,0) =
AQ4,0,ROUNDUP(INDIRECT(ADDRESS(ROW(AS4),COLUMN(AS4) - 1 +
ROUNDUP(AQ4,0)))/2,0)) + AR4".
(Like you said, "...it can get somewhat complex.")
Now you just need to enter "=U4-V4" into cell W4, and you've got your
net score! Happy golfing. :-) |
