Custom Golf Handicaps

PhilLewis

New Member
Joined
Jan 23, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 24, 2003
Messages
1,795
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
What would you expect to have calculated when there are less than 10 scores for a person
 

PhilLewis

New Member
Joined
Jan 23, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I would probably say that 10 scores are needed, so just a 0
 

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,795
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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
Joined
Jan 23, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Here's a screenshot of the first few columns
 

Attachments

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

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,795
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top