How to get top 20% or Low 20%

[vishan_rana]

Dear All,

i Have a data with branch and its TAT as follows,

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Branch[/TD]
[TD="width: 64"]TAT[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]

I want to take A and B's average of highest 20% and average of lowest 20%.

Pls suggest me a formula.

thanks in advance.

Regards
Vishan

 

[J.Ty.]

Hi,

Assume you have 24 items of data, so that 20% is either 4 or 5 items, depending on whether you round 20% down or up. Which of these two possibilities you choose?

J.Ty.

 

[cb366374]

Some sort of array formula would work. Something like this maybe - assuming your range is from A2:B12

=AVERAGEIFS($B$2:$B$12,$A$2:$A$12,D$1,$B$2:$B$12,">="&PERCENTILE(IF($A$2:$A$12=D$1,$B$2:$B$12,""),0.8))

Don't forget Ctrl + Shift + Enter

 

[J.Ty.]

Dear cb366374,

Your solution is wrong. You cannot use percentile. Imagine the following example data, from which you want to get the average of the lowest 20%:

1 2 2 2 2 2 2 2 2 3.


20% means two numbers: in this case 1 and 2, so the desired average is 1.5.

However, if you use percentile, which in this case is 2, you take into account all numbers which are <= than the percentile, so you produce the average of 1 2 2 2 2 2 2 2 2, which is 1.888889.


J.Ty.[TABLE="width: 64"]
<colgroup></colgroup><tbody>[TR]
[TD="width: 64, align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[cb366374]

Good point. Theoretically, they could want to include all the 2's in their average. However, for the more likely scenario that you point out, something like this should work:

for largest 20%
=SUM(LARGE($B$2:$B$21,ROW(INDIRECT("1:"&0.2*COUNTIF($A$2:$A$21,H$1)))))/(0.2*COUNTIF($A$2:$A$21,H$1))

for smallest 20%
=SUM(SMALL($B$2:$B$21,ROW(INDIRECT("1:"&0.2*COUNTIF($A$2:$A$21,H$1)))))/(0.2*COUNTIF($A$2:$A$21,H$1))


There's a probably as easier way to populate an array than the row/indirect/count method I used but it worked for me.

Edit: This is wrong. Grabbed the wrong formula...i'll be back momentarily

 

[J.Ty.]

I produced the following formula for the lowest 20%, in case you round 20% down to the nearest integer:

=(SUMIFS(B1:B20,A1:A20,"A",B1:B20,"<"&SMALL(IF(A1:A20="B",10^30,B1:B20),QUOTIENT(COUNTIF(A1:A20,"A"),5)))+SMALL(IF(A1:A20="B",10^30,B1:B20),QUOTIENT(COUNTIF(A1:A20,"A"),5))*(QUOTIENT(COUNTIF(A1:A20,"A"),5)-COUNTIFS(A1:A20,"A",B1:B20,"<"&SMALL(IF(A1:A20="B",10^30,B1:B20),QUOTIENT(COUNTIF(A1:A20,"A"),5)))))/QUOTIENT(COUNTIF(A1:A20,"A"),5)

It is an array formula, so it should be confirmed with Ctr+Shift+Enter. It assumes that the numbers to be averaged are smaller than 10^30.

J.Ty.

 

[cb366374]

Sorry about that. Here are the correct formulas. Column A would be the Branch column, Column B would be TAT, and cell H1 is just a user input of A or B.

Top 20%
=SUM(LARGE(IF($A$1:$A$20=$G$1,$B$1:$B$20),ROW(INDIRECT("1:"&ROUNDDOWN(0.2*COUNTIF($A$1:$A$20,G$1),0)))))/(ROUNDDOWN(0.2*COUNTIF($A$1:$A$20,G$1),0))

Bottom 20%
=SUM(SMALL(IF($A$1:$A$20=$G$1,$B$1:$B$20),ROW(INDIRECT("1:"&ROUNDDOWN(0.2*COUNTIF($A$1:$A$20,G$1),0)))))/(ROUNDDOWN(0.2*COUNTIF($A$1:$A$20,G$1),0))