Calculating Golf Handicap

[JTL9161]

I have a spreadsheet with historical scores for each golfer. In order to calculate their handicaps I need to take the average of various low scores. Not every golfer has the same amount of completed rounds. Based on the amount of scores each golfer has tells how many scores are needed to calculate the handicap. Here is the breakdown.

3,4 or 5 - Lowest Score
6 , 7 or 8 - Average of lowest 2
9 or 11 Average of lowest 3
12 to 14 Average of lowest 4
15 to 16 Average of lowest 5
17 to 18 Average of lowest 6
19 - Average of lowest 7
20 - Average of lowest 8

I have a cell with the score count for each golfer so that could be used to determine where they fall on the above table.

Need a formula that will look at the number of scores, find the appropriate level on the table and then take the number of scores for that golfer. For example, Golfer A has 18 scores so I need to formula to take the 18 and then look for the lowest 6 scores and average them. All the scores are in a single column so the formula will go at the bottom of that column.

Let me know if their are any questions.
Thank You,
James

 

[Fluff]

You're welcome.