top 20% formula

bflan0524

Board Regular
Joined
Oct 7, 2016
Messages
192
Office Version
  1. 2010
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 AColumn B
% GrowthStart Balance
32.78%$100,700
16.52%$122,245

<tbody>
</tbody>
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Hi, try using the following formula:

Code:
=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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top