AutoRanking then setting average

[AnalystInNeedOfHelp]

Can someone please help me with a formula. I have a list of % figures in a column. I need a formula that will calculate the average of the top 50%.
eg. %'s are in this order:

40%
23%
60%
90%
8%


The % I want would be the average of the top half (ie. the average of 90%, 60%, 40%) which is 63.33%. I can't change the order the %'s sit on to rank them in order of high to low first.

 

[ChrisR]

Is it fixed data? Or will the number of items change?

Based on the above you could do

=AVERAGE(LARGE(C28:C32,{1,2,3}))

Change C28:C32 to your range..............The {1,2,3} picks up the top three values.........(However based on your information above that's 60% not 50%!)

 

[Andrew Poulsom]

Try:

=AVERAGE(IF(RANK(A1:A5,A1:A5)<=CEILING(ROWS(A1:A5)/2,1),A1:A5))

confirmed with Ctrl+Shift+Enter.

 

[Marcelo Branco]

Or maybe

=AVERAGEIF(A:A,">="&LARGE(A:A,ROUNDUP(COUNT(A:A)/2,0)))

M.

 

[Vidar]

If you have an odd set of numbers, should the middle value be included in the average?
This formula will check if you have an odd set of numbers and average the top half including the middle value.
If you have an even set of numbers, it will average the top half.
=IF(ISODD(COUNT(A2:A10)),AVERAGEIF(A2:A10,">="&MEDIAN(A2:A10),A2:A10),AVERAGEIF(A2:A10,">"&MEDIAN(A2:A10),A2:A10))

Replace "A2:A10" range with your range.

 

[AnalystInNeedOfHelp]

Thank you all that's very helpful. I think I will use your formula Vidar because the number of rows will vary and sometimes be odd.