Custom Golf Handicaps

[PhilLewis]

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

 

[jimrward]

What would you expect to have calculated when there are less than 10 scores for a person

 

[PhilLewis]

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

 

[jimrward]

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]

Here's a screenshot of the first few columns

 

[jimrward]

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