How do you calculate the Quintile for every row in Excel?

Question

I'm trying to calculate the quintile for every row of a column in Excel.

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

A value of one will be the top 20%, a value of 5 is the bottom 20%.

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

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

A1 through A131 has the values I'm placing in quintiles.

Thanks

Solution

Your suggested formula works for me......but I think there are several ways you could do this, e.g. use PERCENTILE function to get the breakpoints and then match against those with MATCH, i.e.

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

In most cases that gives the same results as your formula but there might be some discrepancies around the boundaries

Comments

[ChrisG]

Very interesting. A much cleaner formula than what I produced. I did see some differences around the boundary lines, but I'll need to dig to see if I can understand the causes and find which one I like better.