asked on

Have excel average the top x percent of range

I have a range in one column of numbers. I don't want to average the whole range of numbers (~1500 entries), I want to know if there is a way to average a certain percentile.

so for instance if out of the 1500 numbers 160 of them are the top 10% How would I do this?

or If I wanted to know what the average is of the percentile between 10% - 15%.

I imagine that there is a formula where based on my icon set I could specify what the averages for each set of the icon set (that would also be helpful).

Tracy

Averaging The Highest N Numbers In A Range
To average the N largest numbers in a range, use the array formula

=AVERAGE(LARGE(A1:A60,ROW(INDIRECT("1:10"))))

Change "1:10" to "1:N" where N is the number of values to average.
http://www.cpearson.com/Excel/excelF.htm

Also see this thread:
http://excelusergroup.org/forums/t/1866.aspx

Tracy

Remeber, to use the array formula you need to confirm it with Ctrl + Shift + Enter and Excel will add the {} brackets around the formula.

Tracy

See attached example.
Book2.xls

barry houdini

To average between 2 percentiles like 10% and 15% you can use an array formula like this
=AVERAGE(IF(A1:A1600>=PERCENTILE(A1:A1600,10%),IF(A1:A1600<PERCENTILE(A1:A1600,15%),A1:A1600)))
confirmed with CTRL+SHIFT+ENTER
regards, barry

ASKER CERTIFIED SOLUTION

patrickab

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

barry houdini

Yes, Patrick, I reckon you are correct, I took the "between 10% - 15%" too literally......

regards, barry

patrickab

teylyn,

Please apportion the points to reflect the more major contribution made by barryhoudini. After all, I only made a minor correction.

Patrick