How do you calculate the quintile for each row in Excel?

Question

I am trying to calculate the quintile for each row of a column in Excel.

The resulting value for each row in Excel should be 1, 2, 3, 4, or 5.

The value of one will be the top 20%, the value of 5 will be the bottom 20%.

This is my current formula that SEEMS works in, but I'm curious to know if anyone has a better way, UDF or see an error in my formula ...

=ROUNDDOWN(RANK.AVG(A1,$A$1:$A$131,0)/((COUNT(A$1:A$131)+1)/5),0)+1 

From A1 to A131 there are values ​​that I put in quintiles.

thanks

Answer 1

Your proposed formula works for me ...... but I think there are several ways to do this, for example. use the PERCENTILE function to get breakpoints, and then map them to MATCH parameters, i.e.

=MATCH(A1,PERCENTILE(A$1:A$131,{5,4,3,2,1}/5),-1)

In most cases, this gives the same results as your formula, but there may be some border mismatches.

Answer 2

I liked the simplicity =MATCH(A1,PERCENTILE(A$1:A$131,{5,4,3,2,1}/5),-1) . I compared the results with a two-step manual process, which included: 1) calculate 4 percentiles for .2, .4. .6.8 and 2) the combination of nested IF expressions ended up with the same result as = Match the recommended formula in the same 250 values.

IE STEP 1:

 =PERCENTILE.INC($J$3:$J$257,0.2) -> Adjust 0.2 to 0.4; 0.6; 0.8 accordingly 

STEP 2:

 =IF(J3<=$AB$3,5, IF(J3<=$AB$4,4, IF(J3<=$AB$5,3, IF(J3<=$AB$6,2, 1)))) 

Compare each value with the calculated = PERCENTILE.INC in STEP 1