Averaging discreet cells

**Tonymullion** · 11-16-2011, 07:48 AM

I manage data sheets for a golf society. Each week's score is posted to an individual's column. From that column (eventually 52 entries) how do I select the highest 10 (at any time in the year) and average them, showing the answer in an appropriate cell. ie - what's the formula in the answer cell? The point is that the "highest 10" will change troughout the year and I wish to show the onging results. Anbody who says this is sad is automatically disqualifed!!
Thanks folks
Tonymullion

**brokenbiscuits** · 11-16-2011, 08:14 AM

Assuming your range of 52 weeks is in cells A2:A53, you could use:

=IF(COUNT(A2:A53)<10,AVERAGE(A2:A53),(SUMIF(A2:A53,">="&LARGE(A2:A53,COUNTIF(A2:A53,">"&LARGE(A2:A53,10))))+LARGE(A2:A53,10)*(10-COUNTIF(A2:A53,">"&LARGE(A2:A53,10))))/10)

**Tonymullion** · 11-16-2011, 09:57 AM

Thankyou brokenbiscuits - certainly give it a try. just to clarify the question, in the range:-
1 to 16, numbers 7 to 16 are the highest 10 and their average is sum(7 to 16) divided by 10. Thanks

**Tonymullion** · 11-16-2011, 10:26 AM

Hi there Brokenbiscuit. Would never have got there without outside help. Thank you. 1 small point - answer is shown as negative. Pf course, I could always simply place - in front of the formula but is there another "nore proper" solution? Thanks again pal

**MarvinP** · 11-16-2011, 10:46 AM

Hi Tonymullion,

I always like an example to play with, so see the attached for a possible other way.

**brokenbiscuits** · 11-16-2011, 11:13 AM

Hmm, not sure why you're getting a negative - comes out at 11.5 for me.

Have also tested the formula in Marvin's book and get the same results. See attached for confirmation. Similarly, to get the 10 lowest scores, if that's what you're after, you could use:

=IF(COUNT(B2:B53)<10,AVERAGE(B2:B53),(SUMIF(B2:B53,"<="&SMALL(B2:B53,COUNTIF(B2:B53,"<"&SMALL(B2:B53,10))))+SMALL(B2:B53,10)*(10-COUNTIF(B2:B53,"<"&SMALL(B2:B53,10))))/10)

**Tonymullion** · 11-16-2011, 12:09 PM

Originally Posted by brokenbiscuits

Hmm, not sure why you're getting a negative - comes out at 11.5 for me.

Have also tested the formula in Marvin's book and get the same results. See attached for confirmation. Similarly, to get the 10 lowest scores, if that's what you're after, you could use:

=IF(COUNT(B2:B53)<10,AVERAGE(B2:B53),(SUMIF(B2:B53,"<="&SMALL(B2:B53,COUNTIF(B2:B53,"<"&SMALL(B2:B53,10))))+SMALL(B2:B53,10)*(10-COUNTIF(B2:B53,"<"&SMALL(B2:B53,10))))/10)

I'd be dyslexic if I could spell it. It was my errant - sign what did it. Thank you for the extended reply, very useful and once again myy sincere thanks
Regards Tonymullion