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:
The defined names are:
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.
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
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.