# Handicap Score Differentials up to 20 scores

#### andycap

##### New Member
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

#### Attachments

• usga first 20 scores.png
34.2 KB · Views: 19
• Sunday, Quotas, Aug 8, 2021.png
203.4 KB · Views: 19

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### Eric W

##### MrExcel MVP
Something like this:

Book1
1
3313336353338413736293429343930333636333537.3750102
423263331331212
518 2411
62223232510
7232425252621
826272829291720
93131313232320930
102728293031353311240
1112354676.501550
12  1760
1325252728313339323601970
1421233025262930272829322330.2502080
15
16
Sheet2
Cell Formulas
RangeFormula
AC3:AC15AC3=IFERROR(AVERAGE(LARGE(H3:AA3,SEQUENCE(VLOOKUP(COUNT(H3:AA3),AF\$3:AH\$14,2)))),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H3:AA15Expression=H3>=LARGE(\$H3:\$AA3,VLOOKUP(COUNT(\$H3:\$AA3),\$AF\$3:\$AH\$14,2))textNO

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

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

Book1
1
3313336353338413736293629343930333636333537.3750102
423263331331212
518 2411
62223232510
7232425252621
826272829291720
93131313232320930
102728293031353311240
1112354676.501550
12  1760
1325252728313339323601970
1421233025262930272829322330.2502080
15
Sheet2
Cell Formulas
RangeFormula
AC3:AC15AC3=IFERROR(AVERAGE(LARGE(H3:AA3,SEQUENCE(VLOOKUP(COUNT(H3:AA3),AF\$3:AH\$14,2)))),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H3:AA15Expression=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)))))textNO

#### andycap

##### New Member
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

#### Attachments

• freq-usga-test.png
209.4 KB · Views: 3

#### Eric W

##### MrExcel MVP

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:

Excel Formula:
``=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

##### New Member
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:

Excel Formula:
``=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

##### MrExcel MVP
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.

Replies
7
Views
339
Replies
1
Views
108
Replies
5
Views
82
Replies
0
Views
203
Replies
1
Views
76

1,148,253
Messages
5,745,681
Members
423,967
Latest member
malayaka

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back