How to Calculate Golf Handicaps With Excel

The purpose of a golf handicap is to adjust every players' score so they can compete with others on an equal basis. The implementation is a little more complex. Since every golf course poses a different degree of difficulty, the U.S. Golf Association devised formulas that use course rating numbers to produce handicaps that work on any course. The worksheet you will construct uses the USGA prescribed method for computing a handicap. Each column, A though G, will contain one type of data for the rounds. Each row, 2 though 21, will have data for a single round.

Load Excel and open a new workbook sheet. Enter the following column headings in cells A1 though G1: "Date," "Course," "Score," "Rating," "Slope," "Diff." and "Low 10."

In cell F2, enter the formula to compute the differential: “=(C2-D2)*113/E2.”

In cell G2, enter the formula for determining if the differential is one of the lowest 10: “=IF(F2<MEDIAN(F$2:F$21), F2,0).”

Copy the formulas entered in cells F2 and G2 all the way down the columns for the remaining 20 rows used for data entry. F2 will be copied into F3 through F21, and G2 will be copied into G3 through G21.

In cell G22, enter the formula to total the selected differentials: “=SUM(G2:G21).”

In cell H22, enter the formula to compute 96 percent of the average of the 10 differentials: “=G22*0.096."

In cell I22, enter the formula to truncate the computed handicap to the tenths place: “=ROUNDDOWN(H22,1).” This cell contains the computed handicap. You can highlight it with a box by selecting in the Excel menu “Format," "Cell" and "Border." You can also type “Handicap” above it in cell I21.

Hide columns F and G by highlighting them and selecting in the Excel menu “Format," "Column" and "Hide."

Enter the required 20 scores for the player. To add a new score, enter the data over the oldest entry so the old data are deleted. Use the Excel sort function to reorder the entries chronologically. Select the 20 rows of data and sort according to the date column (column A) by selecting in the Excel menu “Data" and "Sort."

Warnings

It is possible, though very unlikely, that fewer than 10 scores could be selected by your worksheet, which would result in an incorrect handicap calculation. This can only happen if there are identical scores from the same golf course and they happen to be exactly equal to the median of all the scores. Each time you enter data, always make sure 10 scores get selected.