Average of top x% of a list

[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!

 

[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