Golf Simulator League - Calculating Golf Handicaps for Google Sheets

[tcmaes]

Good morning. I've only posted on this website a couple times, although I have been an admirer for many years. I am always impressed by the solutions provided by this community.

My question today relates to creating a spreadsheet that automatically calculates the handicap indexes of golfers in my golf league. The handicap index is calculated based on all past golf rounds played. I have a handy table outlining how the handicap index is calculated based on the first round played until any subsequent rounds played.

Currently, I have been using a combination of IF statements, ROUNDDOWN, AVERAGE, and SMALL functions to calculate the handicap indexes. However, as the number of golf rounds increases, this method has become increasingly complex. It is especially challenging when there are 21 or more golf rounds, as this requires calculating the average of the lowest 8 golf rounds out of the last 20 golf rounds.

For the 21+ golf rounds (orange), I have attempted to use an ArrayFormula along with SMALL, OFFSET, LARGE, and ROW functions. However, I have not been able to get it to work consistently, particularly when there are blank cells in between. Moreover, the formula does not seem to work when combined with the IF statements I created for the first 20 golf rounds (green).

If there are any experts who can help me simplify my worksheet to resolve this issue, I would be immensely grateful. Please find an example dataset below and the expected output. Here is also a link to a shared google sheet that I created with four sample datasets: Excel Help

 

[Fluff]

What version of Excel are you using? Or is this for use in Sheets.
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[tcmaes]

Fluff, thank you for your reply. This spreadsheet would live in Google Sheets.

 

[Fluff]

Questions for sheets should be posted in the General Discussion & Other Applications section of the board & it should be made clear what app you are using.
I have done that for you this time.

Unfortunately I don't know enough about Sheets to help.

 

[Fluff]

Whilst this works in xl365 it appears that Sheets does not have the TAKE function

Excel Help.xlsx
	A	B	C	D	E	F					K	L	M	N	O
1
2
3			Golfer 1	Golfer 2	Golfer 3	Golfer 4						New Formula G1	New Formula G2	New Formula G3	New Formula G4
4		Week 1	19	23		17						NER	NER	NER	NER
5		Week 2	15	19		16						NER	NER	NER	NER
6		Week 3	12			12						12	NER	NER	12
7		Week 4	17	13		15						12	13	NER	12
8		Week 5	13	14		11						12	13	NER	11
9		Week 6	17	20		14						12.5	13	NER	11.5
10		Week 7	7									9.5	13	NER	11.5
11		Week 8	14	19		8						9.5	13.5	NER	9.5
12		Week 9	9			19						9.3	13.5	NER	9.5
13		Week 10	13		18	24						9.3	13.5	NER	10.3
14		Week 11	14	16		15						9.3	13.5	NER	10.3
15		Week 12	14	14		12						10.2	13.5	NER	10.3
16		Week 13	6	15		14						8.5	13.6	NER	10.7
17		Week 14	8	13		24						7.5	13.3	NER	10.7
18		Week 15		15								7.5	13.3	NER	10.7
19		Week 16	18	13	15							8.4	13.2	NER	10.7
20		Week 17	12									8.4	13.2	NER	10.7
21		Week 18	23		10							9	13.2	10	10.7
22		Week 19	18									9	13.2	10	10.7
23		Week 20	15									9.5	13.2	10	10.7
24		Week 21	19									10	13.2	10	10.7
25		Week 22	12	17								9.8	13.2	10	10.7
26		Week 23	11	23		10						9.6	13.2	10	10.2
27		Week 24	15	16	9	9						9.7	13.4	9	10
28		Week 25	23		9	16						9.7	13.4	9	10
29		Week 26		14	8	21						9.7	13.4	8.5	10.3
30		Week 27	21	28	9	18						9.7	13.5	8.5	10.3
31		Week 28	12		13	15						9.6	13.5	8.5	10.8
32		Week 29	9	12	5	8						9.8	13.1	7.3	10.5
33		Week 30	9	14	7	10						9.5	13.2	6.6	10
34		Week 31	11	19	12	14						9.7	13.3	6.6	10
35		Week 32	10		5	9						9.5	13.3	6.2	9.6
36		Week 33			5	14						9.5	13.3	5.5	9.6
37		Week 34	11		1	7						9.3	13.3	4	9.1
38		Week 35	11		3	17						9.3	13.3	3.8	9.1
39		Week 36	6			7						9.3	13.3	3.8	9
40		Week 37	4		-1	6						8.8	13.3	2.6	8.2
41		Week 38	13			16						8.8	13.3	2.6	8.2
42		Week 39										8.8	13.3	2.6	8.2
43		Week 40	7	17	6							8.3	13.3	3	8.2
44		Week 41		13	8	8						8.3	13.2	3	8
45		Week 42	12	13	4	17						8.3	13.2	3.1	8
46		Week 43	11	19	4	7						8.3	13.2	3.2	7.6
47		Week 44	10		6							8.2	13.2	3.2	7.6
48		Week 45		21	3							8.2	13.2	3	7.6
49		Week 46										8.2	13.2	3	7.6
Sheet1

Cell Formulas
Range	Formula
L4:O49	L4	=LET(n,{3,6,9,12,15,17,19,20},MAP(SEQUENCE(COUNTA($B$4:$B$200)),LAMBDA(a,LET(d,C4:INDEX(C4:C200,a),f,TAKE(FILTER(d,d<>""),-20),r,COUNT(f),IF(r<3,"NER",ROUNDDOWN(AVERAGE(TAKE(SORT(f),XMATCH(r,n,-1))),1))))))
Dynamic array formulas.

 

[Fluff]

Replaced the TAKE with INDEX & works in xl, but doesn't seem to in Sheets

=LET(n,{3,6,9,12,15,17,19,20},MAP(SEQUENCE(COUNTA($B$4:$B$200)),LAMBDA(a,LET(d,C4:INDEX(C4:C200,a),f,FILTER(d,d<>""),r,COUNT(f),x,INDEX(f,SEQUENCE(MIN(r,20),1,MAX(1,r-19))),IF(r<3,"NER",ROUNDDOWN(AVERAGE(INDEX(SORT(x),SEQUENCE(XMATCH(r,n,-1)))),1))))))

 

[Fluff]

This seems to work

=LET(n,{3,6,9,12,15,17,19,20},MAP(SEQUENCE(COUNTA($B$4:$B$200)),LAMBDA(a,LET(d,C4:INDEX(C4:C200,a),f,FILTER(d,d<>""),r,COUNT(f),x,query(f,"Select * offset "&max(0,r-20)),IF(r<3,"NER",ROUNDDOWN(AVERAGE(SORTN(x,XMATCH(r,n,-1))),1))))))

 

[tcmaes]

Fluff, I appreciate your ongoing assistance. Your latest formula is nearly there.

However, I have noticed that the formula you provided is shifted by one row from what I was anticipating. Please refer to the image below. I was expecting NERs for the first three weeks.

I attempted to modify the formula myself by changing r<3 to r<4, which did resolve the issue with NERs. However, it did not resolve the problem with the other scores. I apologize as I am still in the process of learning Excel/Google Sheets formulas.

Any suggestions? Thanks, Tim.

 

[Fluff]

Why should the 3rd row return NER as there are 3 rounds played & the scoring chart shows that as the minimum value.

 

[tcmaes]

My apologies for not explaining this clearly earlier. The Handicap is calculated prior to the commencement of the round. As an example, the Handicap displayed in the Handicap section of Week 7 reflects the calculation based on the scores from Week 1-6.

To provide a more detailed explanation, please refer to the following breakdown by week. I hope this clears it up a bit.