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]

Something like this:

Book1
	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
2																							Score	Adjustment		# of scores	# to include	Adjustment
3		31	33	36	35	33	38	41	37	36	29	34	29	34	39	30	33	36	36	33	35		37.375	0		1	0	2
4			23	26			33		31														33	1		2	1	2
5					18																			2		4	1	1
6						22		23															23	2		5	1	0
7			23		24				25														25	2		6	2	1
8						26	27			28		29											29	1		7	2	0
9					31	31	31	32	32														32	0		9	3	0
10				27	28	29	30	31	35														33	1		12	4	0
11				1	2	3	5		4	6		7											6.5	0		15	5	0
12																										17	6	0
13				25	25		27	28		31		33		39		32							36	0		19	7	0
14			21		23	30	25		26		29		30		27		28	29	32		23		30.25	0		20	8	0
15
16
Sheet2

Cell Formulas
Range	Formula
AC3:AC15	AC3	=IFERROR(AVERAGE(LARGE(H3:AA3,SEQUENCE(VLOOKUP(COUNT(H3:AA3),AF$3:AH$14,2)))),"")
AD3:AD15	AD3	=IFERROR(VLOOKUP(COUNT(H3:AA3),AF$3:AH$14,3),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H3:AA15	Expression	=H3>=LARGE($H3:$AA3,VLOOKUP(COUNT($H3:$AA3),$AF$3:$AH$14,2))	text	NO

I originally had the table embedded within the formulas, but you can't do that for Conditional Formatting formulas, so I put it into a range (AF3:AH14) which you can put anywhere. I wasn't quite sure what to do with a single score (row 5), so it prints nothing. The other issue is with the Conditional Formatting rule. It checks to see if the value is >= to the nth highest score, but in case of ties, it will highlight more than n. Row 14 has 12 scores, so it should include 4 scores, but the 4th highest is 29, which appears twice, so both are highlighted. There might be a way to fix that, but the score in AC only includes the right amount.

Hope this helps!

 

[Eric W]

Horrible CF formula, but it does address the problem of highlighting duplicate scores:

Book1
	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
2																						Score	Adjustment		# of scores	# to include	Adjustment
3	31	33	36	35	33	38	41	37	36	29	36	29	34	39	30	33	36	36	33	35		37.375	0		1	0	2
4		23	26			33		31														33	1		2	1	2
5				18																			2		4	1	1
6					22		23															23	2		5	1	0
7		23		24				25														25	2		6	2	1
8					26	27			28		29											29	1		7	2	0
9				31	31	31	32	32														32	0		9	3	0
10			27	28	29	30	31	35														33	1		12	4	0
11			1	2	3	5		4	6		7											6.5	0		15	5	0
12																									17	6	0
13			25	25		27	28		31		33		39		32							36	0		19	7	0
14		21		23	30	25		26		29		30		27		28	29	32		23		30.25	0		20	8	0
15
Sheet2

Cell Formulas
Range	Formula
AC3:AC15	AC3	=IFERROR(AVERAGE(LARGE(H3:AA3,SEQUENCE(VLOOKUP(COUNT(H3:AA3),AF$3:AH$14,2)))),"")
AD3:AD15	AD3	=IFERROR(VLOOKUP(COUNT(H3:AA3),AF$3:AH$14,3),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H3:AA15	Expression	=OR(H3>LARGE($H3:$AA3,VLOOKUP(COUNT($H3:$AA3),$AF$3:$AH$14,2)),AND(H3=LARGE($H3:$AA3,VLOOKUP(COUNT($H3:$AA3),$AF$3:$AH$14,2)),COUNTIF($H3:H3,H3)<=VLOOKUP(COUNT($H3:$AA3),$AF$3:$AH$14,2)-COUNTIF($H3:$AA3,">"&LARGE($H3:$AA3,VLOOKUP(COUNT($H3:$AA3),$AF$3:$AH$14,2)))))	text	NO

 

[andycap]

Hey Eric,

Man, you rock! It looks great so far with one exception....it's not adding the "Adjustment" to the Quota. See attached image. I'm using Row 69 with Player "Test 1". I added column "C" as the "Adjustment" column but it's not adding the adjustment to the Quota (column B). I've went through all 20 scores and the formula seems to be working great and so is CF. The only exception is what I mentioned about it adding the Adjustment to the Quota.

You probably noticed that cells AG56:AI67 is the reference range.

Andy

 

[Eric W]

I wasn't sure how to handle the adjustment, which is why I put it in a separate column. You can simply add those 2 columns, or combine the formulas like this:

=IFERROR(AVERAGE(LARGE(H3:AA3,SEQUENCE(VLOOKUP(COUNT(H3:AA3),AF$3:AH$14,2))))+VLOOKUP(COUNT(H3:AA3),AF$3:AH$14,3),"")

 

[andycap]

I wasn't sure how to handle the adjustment, which is why I put it in a separate column. You can simply add those 2 columns, or combine the formulas like this:

=IFERROR(AVERAGE(LARGE(H3:AA3,SEQUENCE(VLOOKUP(COUNT(H3:AA3),AF$3:AH$14,2))))+VLOOKUP(COUNT(H3:AA3),AF$3:AH$14,3),"")

Awesome! I think it's all working now! One more question....I have to do CF on 70 Rows. Is there a way of doing them as a whole or do I have to do one-by-one???

 

[Eric W]

Oh my! You DEFINITELY don't want to do them one-by-one! Especially with a monster formula like that! Just select the entire range you want to put the CF in, H3:AA72 I assume. Then click Conditional Formatting > New Rule > Use a formula, and paste the formula in. Make sure to change the address of the lookup table to match where you have it. Make sure the rest of the addresses refer to the top row of your range, and that the left and right columns are identified properly and with the $ in front. (The formula box is quite small and hard to work with - I often work on the formula in Notepad, then paste the final result in the formula box.) Conditional Formatting formulas work like formulas on the worksheet. If you drag a formula on the worksheet, the addresses are changed to reflect the new relative position, and the same thing is true for CF formulas within the range you selected.

Glad this works for you!

 

[Golface]

Eric, I have similar question.

Except my league uses the lowest scores to calculate an index.

I am trying to get the CF to not highlight multiples of the same number if they are the lowest. If the lowest score is 35 and I have 3 of them, but only want to highlight two of them. I am also having a hard time getting the CF to translate to the next person.

Any help is appreciated.

WHS Handicaps.xlsx
	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
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Master

Cell Formulas
Range	Formula
B3:B4	B3	=IFS(C3=1,((113/61)*(G3-34.55)-2),C3=2,((113/61)*(G3-34.55)-2),C3=3,((113/61)*(G3-34.55)-2),C3=4,((113/61)*(G3-34.55)-1),C3=5,((113/61)*(G3-34.55)-1),C3=6,((113/61)*(G3-34.55)-1),C3>=6,((113/61)*(G3-34.55)))*2
C3:C4	C3	=COUNT(H3:AA3)
D3:D4	D3	=IF(ISNA(INDEX($E$3:$F$22,MATCH(C3,$E$3:$E$22,0),2,FALSE)),0,(INDEX($E$3:$F$22,MATCH(C3,$E$3:$E$22,0),2)))
G3:G4	G3	=IFS(C3=1,(AVERAGE(SMALL(H3:NPX3,{1}))),C3=2,AVERAGE(SMALL(H3:NPX3,{1})),C3=3,AVERAGE(SMALL(H3:NPX3,{1,2})),C3=4,AVERAGE(SMALL(H3:NPX3,{1,2})),C3=5,AVERAGE(SMALL(H3:NPX3,{1,2})),C3=6,AVERAGE(SMALL(H3:NPX3,{1,2})),C3=7,AVERAGE(SMALL(H3:NPX3,{1,2})),C3=8,AVERAGE(SMALL(H3:NPX3,{1,2})),C3=9,AVERAGE(SMALL(H3:NPX3,{1,2,3})),C3=10,AVERAGE(SMALL(H3:NPX3,{1,2,3})),C3=11,AVERAGE(SMALL(H3:NPX3,{1,2,3})),C3=12,AVERAGE(SMALL(H3:NPX3,{1,2,3,4})),C3=13,AVERAGE(SMALL(H3:NPX3,{1,2,3,4})),C3=13,AVERAGE(SMALL(H3:NPX3,{1,2,3,4})),C3=14,AVERAGE(SMALL(H3:NPX3,{1,2,3,4})),C3=15,AVERAGE(SMALL(H3:NPX3,{1,2,3,4,5})),C3=16,AVERAGE(SMALL(H3:NPX3,{1,2,3,4,5})),C3=17,AVERAGE(SMALL(H3:NPX3,{1,2,3,4,5,6})),C3=18,AVERAGE(SMALL(H3:NPX3,{1,2,3,4,5,6})),C3=19,AVERAGE(SMALL(H3:NPX3,{1,2,3,4,5,6,7})),C3>=20,AVERAGE(SMALL(H3:NPX3,{1,2,3,4,5,6,7,8})))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H3:AA150	Cell	contains a blank value	text	YES
H3:AA150	Expression	=H3<=SMALL($H$3:$AA$3, $D$3)	text	NO

 

[Golface]

@Eric W I see you helped out, I hope you get a chance to have a look for me. Maybe help with simpler formulas?

 

[Eric W]

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!