Golf Handicap Calculator

I had a few questions about making an excel sheet to calculate a golf handicap for my personal use. I searched everywhere but couldn't find what I am looking for.

Currently I have 6 columns:
A - Date
B - Course
C - Score
D - Slope
E - Rating
F - Differential

I enter all the information for A-E and then F calculates accordingly. I have all the formulas in and it works fine, but when I add a new score I have to sort the cells and then change a range in the formula to calculate the handicap. Basically I'm looking for a way to find the 20 most recent dates and then using those 20 most recent dates find the 10 best differentials. Any suggestions? Thanks in advance!

We can't really see the issue unless you upload a sample workbook.

As a suggestion, if the range in the formula that you constantly need to change is because you need to add an additional row to it (for example, you need to change A1:A20 to A1:A21 because you added an additional row of data in row 21), you can reference the entire column instead of just the range (Example: Instead of referencing A1:A20, reference A:A). Or instead of hardcoding row 20, you could reference a count of how many cells in the column actually contain data.

From the author’s statement he is treated more leniently than we in Australia! He has 10 best rounds to calculate against our 8.

Irregardless, I’m assuming that all calculations for a day derive a “played to” handicap for the particular Course in question.

My procedure would be to list my last 20 rounds from newest to last.
My macro would then:
1. insert a row/cells at the top of the list to which:
2. the new day’s data would be added.

The 20th set of data has been moved down to 21.
3. Since we don’t need that we select all data within the array of 20 results and sort downwards on the “played to” handicap.
4. In a cell to the side SUM the top 10 “played to” handicaps and then divide by 10
5. Um? Obviously there is need to convert that value to an “absolute” handicap for use at another course where slope will be applied as needed. Record that in a different cell (could be in the table).
6. Reset the sheet to read the new round and the last 19; sort on the date column.
7. remove Row 21, is obsolete and therefore untidy.
8. Close the macro.

If that doesn’t make sense then definitely post a Workbook/sheet.

Hi twig,

Attached is a copy of the spreadsheet I used, until I stopped keeping score last year. The alternative to not keeping score was teaching my clubs to swim.

My calculations track with USGA handicap calculations with the exception that my calculations are occasionally awry by 0.1 due to rounding errors on my part. The latest handicap is in Cell L356. The running values in column 'M' were entered manually based on the numbers in Cell L356.

I hope this helps. If you have any questions about the spreadsheet or the math, please ask.

Lewis

US Golf handicaps use the latest 20 scores. If less than 20 scores are available, the following number of scores are used (5 rounds minimum):
Rounds|Number of scores to use
05-06---1
07-08---2
09-10---3
11-12---4
13-14---5
15-16---6
17-17---7
18-18---8
19-19---9
20-20--10

Lewis

This calculation seems sweet and simple and appears to work well:
=AVERAGE(SMALL(F6:F25,{1;2;3;4;5;6;7;8;9;10}))*0.96
Courtesy of: thejuniorgolfer.com/golf-handicaps-for-kids