Handicap Score Differentials up to 20 scores

[andycap]

Hello,

I have a very nice and detailed Excel database I use for our golf league. In case a golfer sees this, we use the Stableford "points" system rather than "strokes".

Up until now we have kept our averaging simple by only averaging the best 8 scores out of 20. For golfers with less than 8 scores then those scores were used for the average and then the Best 8 kicked in after the 9th round. Now we have to align with the 2020 USGA Handicap Index and therefore there will be different averages and adjustments based on the number of rounds. I've attached an image that showing a chart. The LEFT side of the chart is the USGA chart and then I added the RIGHT side and converted it to points using our points system for our requirements. FYI, this forum provided me the below formula and rule for conditional formatting and that's why I'm back here! Thanks, @Fluff

Currently this is the formula used for averaging the Best 8 out of 20 scores:
=IFERROR(AVERAGE(LARGE($H69:$AA69,SEQUENCE(MIN(8,COUNTIFS($H69:$AA69,">0"))))),0)

I also use Conditional Formatting to highlight the Best 8 scores that are used for the average.
=RANK(H69,$H69:$AA69)+COUNTIFS($H69:H69,H69)-1<=8
Going forward I would need this to adjust as the number of rounds increases and which scores are being used for averaging and, if possible, a way of showing the adjustment.

Looking at the RIGHT side of the chart you can see that for rounds 2 thru 20 there is specific averaging and adjustments after each round. The first round is fixed and doesn't need to be part of the formula. I have no idea how to go about writing a formula to address 20 different averages and adjustments.

The other image is of the sheet used for what we call our "Quotas".

Is this possible?

Thank you,
Andy

 

[Eric W]

I found a shorter (possibly easier?) formula for the Conditional Formatting:

=H3/(H3>0)+COLUMNS($H:H)/1000<=SMALL(FILTER($H3:$AA3+SEQUENCE(,20)/1000,$H3:$AA3>0),$D3)

 

[Golface]

Welcome to MrExcel!

For future reference, it's probably best to open a new thread, so that more people will see it. You can certainly reference the earlier thread, or even tag the helper (as you did in your second message).

But given that, try this:

Book1
	A	B	C	D			G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Combined Men's League's Scores							Most Recent "Adjusted Gross Scores" Provided from Golf Genius
2	Handle	18 hole Index	Round Count	Scores Used			Average Score	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
3	Golfer 1	-4.0	5	2			34.00	33	37	35	36	35
4	Golfer 2	16.2	1	1			40.00	40
Sheet1

Cell Formulas
Range	Formula
B3:B4	B3	=2*(113/61)*(G3-34.55)-LOOKUP(C3,{1,4,7},{4,2,0})
C3:C4	C3	=COUNT(H3:AA3)
D3:D4	D3	=MATCH(C3,{1,3,9,12,15,17,19,20})
G3:G4	G3	=AVERAGE(SMALL(H3:NPX3,SEQUENCE(D3)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H3:AA4	Expression	=LET(s,SMALL($H3:$AA3,$D3),AND(H3<>"",OR(H3<s,AND(H3=s,COUNTIF($H3:H3,s)<=$D3-COUNTIF($H3:$AA3,"<"&s)))))	text	NO

I did simplify some of the formulas, and I used some of the new functionality in Excel 365 to make them easier. Let me know how this works for you!

It does work a lot better, Thanks for your help, and recommendations

 

[Golface]

I found a shorter (possibly easier?) formula for the Conditional Formatting:

=H3/(H3>0)+COLUMNS($H:H)/1000<=SMALL(FILTER($H3:$AA3+SEQUENCE(,20)/1000,$H3:$AA3>0),$D3)

I found a shorter (possibly easier?) formula for the Conditional Formatting:

=H3/(H3>0)+COLUMNS($H:H)/1000<=SMALL(FILTER($H3:$AA3+SEQUENCE(,20)/1000,$H3:$AA3>0),$D3)

Thanks. I didn't show the hidden columns. Is it possible to integrate these, so if i change the scores used if adjusts the formula?

WHS Handicaps.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Combined Men's League's Scores							Most Recent "Adjusted Gross Scores" Provided from Golf Genius
2	Handle	18 hole Index	Round Count	Scores Used	# Games	Scores Used	Average Score	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
3	Golfer 1	1.5	4	2	1	1	35.50	35	36	40	38
4	Golfer 2	19.9	3	2	2	1	41.00	40	42	44
5	Golfer 3	-0.3	6	2	3	2	35.00	32	47	43	44	38	45
6					4	2
7					5	2
8					6	2
9					7	2
10					8	2
11					9	3
12					10	3
13					11	3
14					12	4
15					13	4
16					14	4
17					15	5
18					16	5
19					17	6
20					18	6
21					19	7
22					20	8
23
24
25
26
27
28
Master

Cell Formulas
Range	Formula
B3:B5	B3	=2*(113/61)*(G3-34.55)-LOOKUP(C3,{1,4,7},{4,2,0})
C3:C5	C3	=COUNT(H3:AA3)
D3:D5	D3	=MATCH(C3,{1,3,9,12,15,17,19,20})
G3:G5	G3	=AVERAGE(SMALL(H3:NPX3,SEQUENCE(D3)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H3:AA3,H5:AA5,H4:J4,L4:AA4,H7:AA150,H6:U6,W6:AA6	Cell	contains a blank value	text	YES
H3:AA3,H5:AA5,H4:J4,L4:AA4,H7:AA150,H6:U6,W6:AA6	Expression	=LET(s,SMALL($H3:$AA3,$D3),AND(H3<>"",OR(H3<s,AND(H3=s,COUNTIF($H3:H3,s)<=$D3-COUNTIF($H3:$AA3,"<"&s)))))	text	NO
V6	Cell	contains a blank value	text	YES
V6	Expression	=LET(s,SMALL($H4:$AA4,$D4),AND(V6<>"",OR(V6<s,AND(V6=s,COUNTIF($H4:K4,s)<=$D4-COUNTIF($H4:$AA4,"<"&s)))))	text	NO

 

[Eric W]

Yes, very easy:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Combined Men's League's Scores							Most Recent "Adjusted Gross Scores" Provided from Golf Genius
2	Handle	18 hole Index	Round Count	Scores Used	# Games	Scores Used	Average Score	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
3	Golfer 1	1.5	4	2	1	1	35.50	35	36	40	38
4	Golfer 2	19.9	3	2	2	1	41.00	40	42	44
5	Golfer 3	-0.3	6	2	3	2	35.00	32	47	43	44	38	45
6					4	2
7					5	2
8					6	2
9					7	2
10					8	2
11					9	3
12					10	3
13					11	3
14					12	4
15					13	4
16					14	4
17					15	5
18					16	5
19					17	6
20					18	6
21					19	7
22					20	8
Sheet1

Cell Formulas
Range	Formula
B3:B5	B3	=2*(113/61)*(G3-34.55)-LOOKUP(C3,{1,4,7},{4,2,0})
C3:C5	C3	=COUNT(H3:AA3)
D3:D5	D3	=VLOOKUP(C3,$E$3:$F$22,2)
G3:G5	G3	=AVERAGE(SMALL(H3:NPX3,SEQUENCE(D3)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H3:AA10	Expression	=H3/(H3>0)+COLUMNS($H:H)/1000<=SMALL(FILTER($H3:$AA3+SEQUENCE(,20)/1000,$H3:$AA3>0),$D3)	text	NO

 

[Golface]

@Eric W you are the man! Thank you very much!

 

[Golface]

@Eric W you are the man! Thank you very much!

Is it possible to set up the adjustment in Column "B" to be customizable as well?

 

[Eric W]

Perhaps this B3 formula:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Combined Men's League's Scores								Most Recent "Adjusted Gross Scores" Provided from Golf Genius
2	Handle	18 hole Index	Round Count	Scores Used	# Games	Scores Used	Index Adjustment	Average Score	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
3	Golfer 1	1.5	4	2	1	1	4	35.50	35	36	40	38
4	Golfer 2	19.9	3	2	2	1	4	41.00	40	42	44
5	Golfer 3	-0.3	6	2	3	2	4	35.00	32	47	43	44	38	45
6					4	2	2
7					5	2	2
8					6	2	2
9					7	2	0
10					8	2	0
11					9	3	0
12					10	3	0
13					11	3	0
14					12	4	0
15					13	4	0
16					14	4	0
17					15	5	0
18					16	5	0
19					17	6	0
20					18	6	0
21					19	7	0
22					20	8	0
Sheet1

Cell Formulas
Range	Formula
B3:B5	B3	=2*(113/61)*(H3-34.55)-VLOOKUP(C3,$E$3:$G$22,3)
C3:C5	C3	=COUNT(I3:AB3)
D3:D5	D3	=VLOOKUP(C3,$E$3:$F$22,2)
H3:H5	H3	=AVERAGE(SMALL(I3:NPY3,SEQUENCE(D3)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I3:AB10	Expression	=I3/(I3>0)+COLUMNS($I:I)/1000<=SMALL(FILTER($I3:$AB3+SEQUENCE(,20)/1000,$I3:$AB3>0),$D3)	text	NO

I just added another column to your E:F table, with the adjustment for the number of rounds. Keep in mind that I originally combined the common pieces of the original B formula, and just added the subtraction piece for the differences. If the formula is more complicated that, we'd need to reexamine the formula.

 

[Eric W]

It's pretty easy to extend this sheet to include more than 20 rounds.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	Combined Men's League's Scores								Most Recent "Adjusted Gross Scores" Provided from Golf Genius
2	Handle	18 hole Index	Round Count	Scores Used	# Games	Scores Used	Index Adjustment	Average Score	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25
3	Golfer 1	1.519672	4	2	1	1	4	35.5	35	36	40	38
4	Golfer 2	19.89672	3	2	2	1	4	41	40	42	44
5	Golfer 3	20.65492	25	8	3	2	4	40.125	32	47	43	44	38	45	41	42	43	44	45	46	47	48	49	49	48	47	46	45	44	43	42	48	40
6					4	2	2
7					5	2	2
8					6	2	2
9					7	2	0
10					8	2	0
11					9	3	0
12					10	3	0
13					11	3	0
14					12	4	0
15					13	4	0
16					14	4	0
17					15	5	0
18					16	5	0
19					17	6	0
20					18	6	0
21					19	7	0
22					20	8	0
Sheet16

Cell Formulas
Range	Formula
B3:B5	B3	=2*(113/61)*(H3-34.55)-VLOOKUP(C3,$E$3:$G$22,3)
C3:C5	C3	=COUNT(I3:ZZ3)
D3:D5	D3	=VLOOKUP(C3,$E$3:$F$22,2)
H3:H5	H3	=AVERAGE(SMALL(I3:ZZ3,SEQUENCE(D3)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I3:ZZ10	Expression	=I3/(I3>0)+COLUMNS($I:I)/1000<=SMALL(FILTER($I3:$ZZ3+SEQUENCE(,COLUMN($ZZ1)-COLUMN($H1))/1000,$I3:$ZZ3>0),$D3)	text	NO

Just extend the C3 and H3 formulas to look at a column further to the right (I picked ZZ). The Conditional Formatting formula also needs to be adjusted. I tweaked it a little to make it more obvious how to adjust the last column. Let me know how this works!

 

[Golface]

@Eric W I know this is older. It has been working like a charm. My question now is can it be set to use the latest 20 scores if i enter over 20 scores?

 

[Golface]

It's pretty easy to extend this sheet to include more than 20 rounds.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	Combined Men's League's Scores								Most Recent "Adjusted Gross Scores" Provided from Golf Genius
2	Handle	18 hole Index	Round Count	Scores Used	# Games	Scores Used	Index Adjustment	Average Score	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25
3	Golfer 1	1.519672	4	2	1	1	4	35.5	35	36	40	38
4	Golfer 2	19.89672	3	2	2	1	4	41	40	42	44
5	Golfer 3	20.65492	25	8	3	2	4	40.125	32	47	43	44	38	45	41	42	43	44	45	46	47	48	49	49	48	47	46	45	44	43	42	48	40
6					4	2	2
7					5	2	2
8					6	2	2
9					7	2	0
10					8	2	0
11					9	3	0
12					10	3	0
13					11	3	0
14					12	4	0
15					13	4	0
16					14	4	0
17					15	5	0
18					16	5	0
19					17	6	0
20					18	6	0
21					19	7	0
22					20	8	0
Sheet16

Cell Formulas
Range	Formula
B3:B5	B3	=2*(113/61)*(H3-34.55)-VLOOKUP(C3,$E$3:$G$22,3)
C3:C5	C3	=COUNT(I3:ZZ3)
D3:D5	D3	=VLOOKUP(C3,$E$3:$F$22,2)
H3:H5	H3	=AVERAGE(SMALL(I3:ZZ3,SEQUENCE(D3)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I3:ZZ10	Expression	=I3/(I3>0)+COLUMNS($I:I)/1000<=SMALL(FILTER($I3:$ZZ3+SEQUENCE(,COLUMN($ZZ1)-COLUMN($H1))/1000,$I3:$ZZ3>0),$D3)	text	NO

Just extend the C3 and H3 formulas to look at a column further to the right (I picked ZZ). The Conditional Formatting formula also needs to be adjusted. I tweaked it a little to make it more obvious how to adjust the last column. Let me know how this works!

is there a way to get rid of the first score if i enter a 21st score for example, I only need to use the most recent 20 scores