Average of lowest scores

I'm trying to create a formula to calculate average golf scores.

Sounds easy enough but I only want to take the average of the lowest 8 scores.

I have managed to combine RANK, IF and AVERAGE to some degree but I am still having problems where I have duplicate scores.

Any ideas?

Hi,

With an array function ... i.e Control Shift Enter instead of Enter ...

Please Login or Register  to view this content.

HTH
Carim

Sorry could you expand a little?


thanks

Expand on what ...

1. the array function :
once formula is typed, use Control Shift Enter instead of Enter

2. the range A1:A50 is an assumption, you have to adjust it to your specific situation...

3. small(yourrange,8) 8 because you need the 8 smallest numbers ...

HTH
Carim

Sorry I missed the "Duplicate scores" bit ...

You need to apply the formula to Unique Values for an accurate result ...

HTH
Carim

Hi,

Attached is a sample worksheet to work out Average of Smallest Unique values ...

HTH
Carim

Glad it fixed your problem

Carim

thanks for your help....

However, i am not sure that I can use your solution as I will not have a list of unique values...

e.g.

69
70
71
72
73
74
75
76
76

76 would be ranked 8th smallest but would be included in the list twice. So the average would be 72.89 (9 scores) instead of 72.5 (8)...

Yes ...
But did you take a look at the sample worksheet posted above ...?

Carim

My apologies I thought this was only for unique values but I have applied my example and it works perfectly.

Thanks very much.

Merry Christmas!

I am not sure that the proposed solution works as you require the lowest 8 scores for a handicap so in your example if you had 2 71's they would both be included in the average in the solution given this would not be the case.
Perhaps if the data was in e2:e13 the following

=AVERAGE(SMALL(E2:E13,1),SMALL(E2:E13,2),SMALL(E2:E13,3),SMALL(E2:E13,4),SMALL(E2:E13,5),SMALL(E2:E13,6),SMALL(E2:E13,7),SMALL(E2:E13,8))

Regards

Dav

Glad it fixed your problem.

Thanks for the feedback

Carim