# Custom Golf Handicaps

#### PhilLewis

##### New Member
I am the commissioner of a disc golf league and we want to start using handicaps to level the playing field. Golf courses have both course ratings as well as slope ratings, but these don't exist for disc golf, so have top create my own formula. We use 54 as our standard par score at all courses I have data for. I would like to calculate handicaps as the average of the last 10 rounds played minus the highest and lowest scores. My data is arranged in columns. So, column A is course name, column B is date, and each column after that is scores for each player. Not every player played every round, so there are blanks or "no score" in some cells. I would like to write this in a way that will automatically update as I add in new data.

Thanks,
Phil

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### jimrward

##### Well-known Member
What would you expect to have calculated when there are less than 10 scores for a person

#### PhilLewis

##### New Member
I would probably say that 10 scores are needed, so just a 0

#### jimrward

##### Well-known Member
I have something similar setup up for shooting scores where I am looking for best 5 from 6
my data is arranged name, s1,s2,s3 etc to s20 where there are less than a window of last 6 scores it takes a straight average of what is there
I will need to get home and fire up windows machine to see whether I can adapt
can you elaborate on your layout a bit more

#### PhilLewis

##### New Member
Here's a screenshot of the first few columns

#### Attachments

• Screenshot (3).png
34.1 KB · Views: 7

#### jimrward

##### Well-known Member
I have tried to modify the formula I was given on here for the layout of your data and failed miserably
it is an array formula and it is causing me grief
here it is anyway for data going horizontally rather than vertically, also I just have blank cells where there is no score not text as it your data,
my data is 20 columns across and this gives me the average of the best 5 scores from 6 from the most current data so if you have 7 weeks of data it drops weeks 1 and 2 etc, if there are less then 6 scores the straight average is taken

=IF(COUNTIF(B2:U2,">0")>5,AVERAGE(LARGE(INDEX(B2:U2,LARGE(IF(B2:U2<>"",COLUMN(B2:U2)-COLUMN(B2)+1),6)):U2,{1,2,3,4,5})),IFERROR(AVERAGEIF(B2:U2,">0"),0))

the above is entered with control shift enter

Replies
2
Views
147
Replies
6
Views
275
Replies
7
Views
339
Replies
7
Views
381
Replies
4
Views
239

1,148,368
Messages
5,746,287
Members
424,006
Latest member
Metal_warrior

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