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

Column A	Column B
% Growth	Start Balance
32.78%	$100,700
16.52%	$122,245

<tbody>
</tbody>

 

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