Golf Handicap
The USGA has a formula to compute your handicap and the people who compute my handicap, GHIN, only do so about twice per month. I have no idea why it’s so infrequently, but they probably have their reasons. In the meantime, I’m trying to replicate the formula in Excel.
The formulas are:
$D$5 =TRUNC((SUMIF(HistUsed,"?",HistDiff)/COUNTIF(HistUsed,"?"))*0.96,1)
$G$5 =ROUND(D5/BOGEYSLOPE*F5,0)
$C$9 =IF(OR(D9=”",D9<LARGE(HistDate,MIN(20,COUNT(HistDate)))),”",
IF(H9<=SMALL(((HistDiff)*IF((HistDate>=LARGE(HistDate,MIN(20,COUNT(HistDate)))),1,1000)),
VLOOKUP(COUNT(HistDate),ScoresUsed,2)),”*”,”"))
$H$9 =IF(D9=”",”",ROUND((E9-F9)*BOGEYSLOPE/G9,1))
The defined names are:
BOGEYSLOPE =113
HistDate =OFFSET(ScoreHistory!$D$8,1,0,COUNT(ScoreHistory!$D$9:$D$64987),1)
HistDiff =OFFSET(ScoreHistory!$H$8,1,0,COUNT(ScoreHistory!$D$9:$D$64987),1)
HistUsed =OFFSET(ScoreHistory!$C$8,1,0,COUNT(ScoreHistory!$D$9:$D$64987),1)
ScoresUsed =Lists!$A$1:$B$11
There are some things missing from this calculation, including 9-hole scores and tournament scores, but it’s a start. I haven’t had the opportunity to check it against the GHIN’s calc yet.
In the D5 formula, I had to SUMIF but I was looking for a wildcard character, namely the asterisk. I don’t know how to SUMIF for an asterisk. Since that column can only have an asterisk or an empty cell, I used the question mark to essentially determine if it had a one character string in it or not. I was pretty proud of that, but I still would like to know how to do it right.
I’m going to try to break down the C9 formula in English.
1 =IF(
2 OR(
3 D9=”",
4 D9<LARGE(
5 HistDate,
6 MIN(
7 20,
8 COUNT(HistDate)
9 )
10 )
11 ),
12 ”",
13 IF(
14 H9<=SMALL(
15 ((HistDiff)*IF(
16 (HistDate>=LARGE(
17 HistDate,
18 MIN(20,COUNT(HistDate))
19 )),
20 1,
21 1000)),
22 VLOOKUP(
23 COUNT(HistDate),
24 ScoresUsed,
25 2
26 )),
27 ”*”,
28 ”"))
Boy, that was a lot of work and it’s not any easier to follow. Oh well.
Line 1-12: If there is no date or the date is not one of the 20 most recent dates, then return an empty string.
Line 4-8: Find the 20th largest date (or a less than the 20th) and compare that to the date.
Line 6-8: If there are less than 20 entries, then include all the dates, otherwise just the most recent 20.
Line 13: This starts the Else from Line 1. If the date isn’t blank AND the date is within the last 20 dates, then put an asterisks in the cell if the differential is one of the smallest differentials.
Line 22: The smallest differentials will generally be the smallest 10 of the last 20, but if there’s less than 20 scores, the number is different. It’s defined in the ScoresUsed defined name and this looks it up.
Line 14-21: If this differential is less than the xth smallest differential… I start with all the differentials but I need to exclude any that are older than 20 scores old. Normally, you can use an array formula to make those score zero, but I’m in the middle of a SMALL function and I would essentially be making those the smallest (at zero) which screws everything up. Instead, I put an IF in there. If the date is too old, I multiply the differential by 1000 instead of by 1, which will make it so large that it won’t be a factor in the SMALL function.
Clear as mud, huh? Remind me never to explain formulas again.
Update: Because WordPress screws up the quotes, some people are having trouble recreating this worksheet. You can download the workbook Handicap.zip.
Update: I fixed the above download to 1) Handle ties in the differential 2) Handle ties in the date and 3) Protect the formulas.
C.G. Ouimet:
I’m not sure why you used the “*”. Why not use “X” instead so that the SimIf works as intended?
18 August 2005, 10:34 amDick Kusleika:
The asterisk is what GHIN uses and I wanted to keep it consistent. Besides, “X” would have been a cop out, right?
18 August 2005, 12:29 pmRob van Gelder:
Dick,
The escape character for wildcards is the tilde:
=SUMIF(A1:A4, “~*”, B1:B4)
From Excel’s Help, search for Wildcard.
Cheers,
18 August 2005, 1:40 pmRob
Dick Kusleika:
Thanks Rob. There’s this new fangled blog that I should be reading to get this kind of information
http://www.dicks-blog.com/archives/2004/05/25/finding-wildcards/
18 August 2005, 2:37 pmVasant:
>>Line 14-21:I need to exclude any that are older than 20 scores old. Normally, you can use an array formula to make those score zero, but I’m in the middle of a SMALL function and I would essentially be making those the smallest (at zero) which screws everything up. Instead, I put an IF in there. If the date is too old, I multiply the differential by 1000 instead of by 1, which will make it so large that it won’t be a factor in the SMALL function.
21 August 2005, 7:30 pmVasant:
What I meant to say was:
Being a purist, I dislike using an arbitrary number in any formula. I would set the value of the old scores to null strings, instead of multiplying them by 1000. I know … picky, picky, picky!
21 August 2005, 7:32 pmMike Bouvier:
I couldn’t get this to work for 6 scores(not an excel expert), because the SMALL function in the $C$9 formula was expecting an array for the first argument, but was getting a value instead. I changed it to:
26 August 2005, 6:25 am=IF(OR(D9=”",D9=LARGE(HistDate,MIN(20,COUNT(HistDate)))),1,1000)
J.P. Doffing:
Nice Work Dick! I was looking for the formula for how they came up with my Handicap as GHIN doesn’t display trend. So… I’ll be writing a little website program to track my scores and such. Thanks!
31 August 2005, 11:51 amJohn:
Hey Dick,
9 January 2006, 10:23 amI can’t download the workbook. Would love to use this this tool if possible. Sounds great.
Dick Kusleika:
John: should be fixed up now.
9 January 2006, 12:33 pmJos'h Hancher:
It seems, that there is a bug.
If you have say an hcp diff of 17.9 and that happens to be your highest in you last 20 but occurs more than once in you last 20 it counts all of them. Giving you more than 10 best out of you last 20
25 January 2006, 8:38 pmChris Stoughton:
Thank you for the excellent handicap spreadsheet. Your local golf association should have ratings for each nine of a golf course. I am using it that way and it works great.
11 May 2006, 5:45 pmStephen:
Thanks for the Handicap Spread sheet. I will be sure to let others know of your website.
15 September 2006, 9:45 pmDavid:
Thanks. However, I’m just curious about the Index calculation. Why is it truncated instead of rounded at the tenths place?
23 April 2007, 2:42 amDavid:
Never mind; I see the USGA handicap rules call for the calculation to be truncated. Great job, and thanks again!
23 April 2007, 9:19 ammike shanahan:
Hi dick, i am having trouble with an excel workbook. I need to calculate the month to date of call logs, but am unsure how to do this.I need the list to refresh after 4 weeks, and begin again.
5 June 2007, 7:33 amMark McNamee:
I would like to insert a column so that I can insert the name of the course but the file is password protected. can you provide the password?
28 March 2008, 6:51 pmDick Kusleika:
The file isn’t protected and there is no password. The sheet is protected, but not by a password. Try Tools - Protection - Unprotect Sheet.
31 March 2008, 7:14 amJuris:
Your spread sheet doesn’t account for tournament scores as the GHIN does.
24 June 2008, 11:13 am