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]

If you just want to keep adding scores to the right:

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	28.99098	25	8	3	2	4	42.375	32	47	43	44	38	45	41	42	43	44	45	46	47	48	49	49	48	47	46	45	44	43	42	44	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(INDEX(3:3,MAX(0,C3-20)+COLUMN(I3)):ZZ3,SEQUENCE(D3)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I3:ZZ10	Expression	=I3/(I3>0)/(COLUMNS($I3:I3)>$C3-20)+COLUMN(I:I)/1000<=AGGREGATE(15,6,($I3:$AZ3+COLUMN($I3:$AZ3)/1000)/($I3:$AZ3<>"")/(COLUMN($I3:$AZ3)-COLUMN($I3)+1>$C3-20),$D3)	text	NO

Only the H3 formula on the sheet changed. The Conditional Formatting formula was quite tricky to figure out though.

 

[Eric W]

On the Conditional Formatting formula, you should change the rightmost column from AZ to ZZ to match the H3 formula. I changed it while testing and forgot to change it back.

 

[Golface]

On the Conditional Formatting formula, you should change the rightmost column from AZ to ZZ to match the H3 formula. I changed it while testing and forgot to change it back.

Sorry I tried am not sure what you mean? For this step

 

[Golface]

Sorry I tried am not sure what you mean? For this step

I should have read that before I sent. I don't understand what you mean by changing the "rightmost column from AZ to ZZ to match the H3 formula.", I have tried and it doesn't seem to work for me. Thanks for all of your help.

 

[Eric W]

I just meant that the H3 formula has a rightmost column of ZZ:

=AVERAGE(SMALL(INDEX(3:3,MAX(0,C3-20)+COLUMN(I3)):ZZ3,SEQUENCE(D3)))

and the CF formula has a rightmost column of AZ:

=I3/(I3>0)/(COLUMNS($I3:I3)>$C3-20)+COLUMN(I:I)/1000<=AGGREGATE(15,6,($I3:$AZ3+COLUMN($I3:$AZ3)/1000)/($I3:$AZ3<>"")/(COLUMN($I3:$AZ3)-COLUMN($I3)+1>$C3-20),$D3)

They both work, but the CF formula will only work for an additional 25 columns (scores), while the H3 formula is good for about 700 more.

 

[Golface]

I just meant that the H3 formula has a rightmost column of ZZ:

=AVERAGE(SMALL(INDEX(3:3,MAX(0,C3-20)+COLUMN(I3)):ZZ3,SEQUENCE(D3)))

and the CF formula has a rightmost column of AZ:

=I3/(I3>0)/(COLUMNS($I3:I3)>$C3-20)+COLUMN(I:I)/1000<=AGGREGATE(15,6,($I3:$AZ3+COLUMN($I3:$AZ3)/1000)/($I3:$AZ3<>"")/(COLUMN($I3:$AZ3)-COLUMN($I3)+1>$C3-20),$D3)

They both work, but the CF formula will only work for an additional 25 columns (scores), while the H3 formula is good for about 700 more.

So I should change the CF formula from the AZ3 to ZZ3 if I would like to continue past 25 spots. Thats great.