Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average a list with high and low ignored | Excel Worksheet Functions | |||
Average of a list with high and low ignored | Excel Discussion (Misc queries) | |||
Average a List and sorting | Excel Worksheet Functions | |||
How can I average data from a repeating list into a unique list? | Excel Worksheet Functions | |||
How can I average data from a repeating list into a unique list? | Excel Worksheet Functions |