Calculating Golf Handicap

[JTL9161]

I have a spreadsheet with historical scores for each golfer. In order to calculate their handicaps I need to take the average of various low scores. Not every golfer has the same amount of completed rounds. Based on the amount of scores each golfer has tells how many scores are needed to calculate the handicap. Here is the breakdown.

3,4 or 5 - Lowest Score
6 , 7 or 8 - Average of lowest 2
9 or 11 Average of lowest 3
12 to 14 Average of lowest 4
15 to 16 Average of lowest 5
17 to 18 Average of lowest 6
19 - Average of lowest 7
20 - Average of lowest 8

I have a cell with the score count for each golfer so that could be used to determine where they fall on the above table.

Need a formula that will look at the number of scores, find the appropriate level on the table and then take the number of scores for that golfer. For example, Golfer A has 18 scores so I need to formula to take the 18 and then look for the lowest 6 scores and average them. All the scores are in a single column so the formula will go at the bottom of that column.

Let me know if their are any questions.
Thank You,
James

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[JTL9161]

For this golfer there are 20 scores so I need the formula to use the 20 to calculate the average of 8 of the lowest scores. Including duplicate scores.

GOLFER A	1	2	3	4	5	6	7	8	9	OUT	10	11	12	13	14	15	16	17	18	IN	TOTAL
Par	4	5	4	3	4	4	4	3	4		4	4	5	4	3	4	5	3	4	36	72
	6	7	5	4	6	5	6	4	5	48											48
											5	9	7	5	3	4	7	3	4	47	47
	7	9	5	5	5	5	6	5	6	53											53
											6	6	7	7	3	6	5	4	5	49	49
	6	8	6	6	5	5	6	5	5	52											52
											7	7	7	6	4	6	6	5	5	53	53
	5	6	6	3	6	5	6	4	5	46											46
											7	6	6	7	4	6	7	5	6	54	54
	6	6	7	5	6	4	5	4	5	48											48
											4	4	7	5	4	6	8	5	5	48	48
	6	6	6	5	4	6	6	3	4	46											46
											6	7	7	6	4	5	5	4	7	51	51
	8	8	5	5	5	4	6	4	5	50											50
	6	7	7	5	6	6	4	3	6	50											50
											6	7	6	7	4	6	5	4	7	52	52
											4	4	7	7	5	4	4	3	6	44	44
	5	7	6	7	4	5	5	4	7	50											50
											7	5	8	6	5	6	6	3	6	52	52
	7	6	5	5	6	5	5	5	5	49											49
											4	6	8	5	3	4	6	5	4	45	45
																				# of Rds.	20

 

[Fluff]

How about

=LET(s,SORT(FILTER(V3:V22,V3:V22<>"")),AVERAGE(TAKE(s,LOOKUP(ROWS(s),{3,6,9,12,15,17,19,20},SEQUENCE(,8)))))

 

[JTL9161]

WOW! Works great, Thanks.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[JTL9161]

Question. I have a golfer with 19 scores so I take the 7 lowest scores. The lowest scores are 43-44-44-45-45-46-46-46. Does your formula take the lowest 7 scores regardless of duplicates? Meaning they take both 44's both 45's and the 3 46's? I ask because your formula gave me 44.0 average but when I take the above 7 scores and average it I get 44.71.

 

[JTL9161]

Question. I have a golfer with 19 scores so I take the 7 lowest scores. The lowest scores are 43-44-44-45-45-46-46-46. Does your formula take the lowest 7 scores regardless of duplicates? Meaning they take both 44's both 45's and the 3 46's? I ask because your formula gave me 44.0 average but when I take the above 7 scores and average it I get 44.71.

Sorry, me again. Wrong column. But could you answer the duplicate question?

 

[Fluff]

It will take the 43, both 44s, both 45s & two 46s to give 44.71

Fluff.xlsm
	V
3	43
4	44
5	44
6	45
7	45
8	46
9	46
10	46
11	48
12	48
13	46
14	51
15	50
16	50
17	52
18	55
19	50
20	52
21	49
22
23
24	44.71
Sheet6

Cell Formulas
Range	Formula
V24	V24	=LET(s,SORT(FILTER(V3:V22,V3:V22<>"")),AVERAGE(TAKE(s,LOOKUP(ROWS(s),{3,6,9,12,15,17,19,20},SEQUENCE(,8)))))

 

[JTL9161]

Thank you