If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Average of top x% of a list
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! |
#2
|
|||
|
|||
Average of top x% of a list
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 "Brendan" wrote in message ... 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! |
Thread Tools | |
Display Modes | |
|
|