top 20% formula

[bflan0524]

hello
i have a range of data in column A, A2:A159, i am trying to find out the average for the top 10%, top 25% middle 50% and bottom 25%. is there a formula i can use for this, example below

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]% Growth[/TD]
[TD]Start Balance[/TD]
[/TR]
[TR]
[TD]32.78%[/TD]
[TD]$100,700[/TD]
[/TR]
[TR]
[TD]16.52%[/TD]
[TD]$122,245[/TD]
[/TR]
</tbody>[/TABLE]

 

[JustynaMK]

Hi, try using the following formula:

=SUMPRODUCT((--([COLOR=#ff8c00]$A$1:$A$20[/COLOR]>=PERCENTILE([COLOR=#ff8c00]$A$1:$A$20[/COLOR],[COLOR=#ff0000]0.75[/COLOR])))*[COLOR=#ff8c00]$A$1:$A$20[/COLOR]/COUNTIF([COLOR=#ff8c00]$A$1:$A$20[/COLOR],">="&PERCENTILE([COLOR=#ff8c00]$A$1:$A$20[/COLOR],[COLOR=#ff0000]0.75[/COLOR])))

It uses a range of the first 20 cells and returns the average for the top 25% (0.75). Please change the range and Percentile in order to see the results for the remaining percentages (i.e. top 10% - 0.9, top 25% - 0.75, top 50% - 0.5, top 75% - 0.25).

Hope it helps.