Average bet 7 scores

[vgiesbrecht]

I am looking for a way to average the best 7 scores althought there may be 15 or 20 scores. I hope to spread over mutiple sheets. I currently am using the threed functions to add up all scores and counts but I am wanting to average only the top 7 scores.

Can this be done?

 

[firefytr]

Hi there,

=AVERAGE(IF(RANK(A1:A10,A1:A10,1)<=D1,A1:A10))

... entered with CTRL + SHIFT + ENTER, as opposed to just ENTER, as it is an array formula. This is where A1:A10 is your data housing and D1 is 7 (or whatever top n you'd like to average). Note this doesn't take into account ties. If you want to take ties into account, use an additional column, enter this in (I'll just say B1)...

=COUNT($A$1:$A$10)-(RANK(A1,$A$1:$A$10)+COUNTIF($A$1:A1,A1)-1)+1

.. copy down to B10 (or the last cell adjacent to where your data is housed). Then your formula becomes...

=AVERAGE(IF(RANK(A1:A10,B1:B10,1)<=D1,A1:A10))

.. also confirmed with CTRL + SHIFT + ENTER.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci