Back in 2005, I created a spreadsheet to calculate my USGA handicap and I’ve been using it ever since (175 scores posted). It calculates my current handicap but doesn’t give me any historical information. Until now.

GHIN calculates handicaps on a schedule, not in real time. That means many of the handicaps on my spreadsheet aren’t official. They do that so that one score doesn’t swing the calculation too much. By calculating approximately monthly, they give you a chance to post a few scores between calculations. Here’s what my spreadsheet looks like today:

The formulas up to column J are the same as they were in 2005. I’ve added formulas in K through O.

`K181: =SUMPRODUCT(((H162:H181+(ROW(H162:H181)/100000))<=SMALL(H162:H181+(ROW(H162:H181)/100000),10))*(H162:H181))`

The part that says `+(ROW(H162:H181)/100000)`

is used to add a small number so that I can break ties but won't affect the score. Otherwise it's summing up the 10 smallest of the last 20. I started 20 rows down so I wouldn't have to mess with less than 20 scores. That's too much work for not enough payoff.

`L181: =SUMPRODUCT(--((H162:H181+(ROW(H162:H181)/100000))<=SMALL(H162:H181+(ROW(H162:H181)/100000),10)))`

I don't know why I wrote this one - it's always 10. Except that sometimes it was 11 before I added the +ROW stuff to break ties. It's principally the same formula as the previous one except for the last part. And it includes the double-negation to coerce the Trues and Falses into numbers.

`M181: =TRUNC(K181/L181*0.96,1)`

Divide one by t'other and take 96%.

`N181: =IF((M181+(ROW(M181)/100000))=MIN(IF(((YEAR($D$27:$D$181)=YEAR(D181))*($M$27:$M$181))=0,"",($M$27:$M$181+(ROW($M$27:$M$181)/100000)))),M181,NA())`

O181: =IF((M181+(ROW(M181)/100000))=MAX((YEAR($D$27:$D$181)=YEAR(D181))*(($M$27:$M$181+(ROW($M$27:$M$181)/100000)))),M181,NA())

Once I got that part done, the data was begging to be graphed. Once on a chart, I could see that I needed some Min's and Max's to clutter it up. These last two formulas compute the minimum and maximum indexes by year and return that value on the row where it exists. If it doesn't exist on a particular row, it returns NA() so it doesn't show up on the chart.

To make the chart, I selected all the data from D:O and created an XY chart. Then I deleted all the series that I didn't want. Sorry for the 2003isness of the chart. Some of the steps I took to create this chart are:

- CumIndex: Remove markers, make Olive Green line, add Polynomial2 trendline
- Trendline: Make 25% grey
- Major Gridlines: Make 25% grey
- Min: Remove lines, make makers green, make data labels = Y values, put data labels on bottom
- Max: Remove lines, make makers red, make data labels = Y values, put data labels on top
- Y axis: Make minimum of 4 and maximum of 16 to get rid of some white space
- X axis: Adjust min and max to tighten it up
- Plot area: Remove fill

Oh, how I pine for the days of a 7.5 handicap index. The season in Nebraska ends November 15, so I have a little more than two months to get that down. I'd like to get it down to, at most, an 8.0 by then.

You can download Handicap2.zip

Great post!

Great formulas. They are certainly complex with all of the variations and that only certain scores are used.

Does your spreadsheet take into account 9 hole rounds for your index calculation?

I am in Denver and a 11.6 index, so let me know if you are ever in the area and hopefully we can play at my club in Lakewood.

How was your work effected by the finanical crisis? If we say there is a lag between the amount of practice and the reduction in handicap could that explain the minimum in June 2009?

Thanks Steve=True. I’ll look you up when I’m in Denver next and we can play a round.

James: There is a direct and obvious relationship between my practice time and my scores. In 2009 I was hitting 200 balls a week on the range and spending two hours a week on the chipping green. Not so much since then – although I’ve stepped it up recently.