Average of top x% of a list

B

Brendan

I need a flexible formula to find the average of the top x% of data in tables
of varying size.

To be more specific, I have daily data for many years and I need to
calculate the average if the top x% of the data. To make it more complicated,
each year is missing a different number of data points, so this formula needs
to calculate how many points to average.

I've played with Sumif/countif, percentile and Large, but can't get anything
to work. Any help would be appreciated!
 
B

Bernie Deitrick

Brendan,

Use an array formula like this, entered using Ctrl-Shift-Enter (with your data in cells A2:A2000)

=AVERAGE(IF(A2:A2000>=PERCENTILE(A2:A2000,1-C2),A2:A2000))

Enter 10% into C2 to get the average of the top 10%, 20% to get the top 20%, etc.

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finding the weighted average of text that have been given values 1
Trying to find additional ranges outside of min, max, and average 2
Calculate average in intervals 1
Finding average of top 70% of the values 1
Average with criteria for adjacent cells ? 1
Creating a Search using a combo box and then creating a report. 4
Using a Date and corresponding weekday to pull amounts 1
Average range of discontinuous cells 1

Top