# AutoRanking then setting average

#### AnalystInNeedOfHelp

##### New Member
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.

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### ChrisR

##### Well-known Member
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

##### MrExcel MVP
Try:

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

confirmed with Ctrl+Shift+Enter.

#### Marcelo Branco

##### MrExcel MVP
Or maybe

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

M.

#### Vidar

##### Well-known Member
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

##### New Member
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.

Replies
3
Views
433
Replies
11
Views
342
Replies
1
Views
121
Replies
3
Views
242
Replies
5
Views
327

1,195,651
Messages
6,010,929
Members
441,573
Latest member
Goronvir

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back