Quartile Function (ranking lowest values 1st quartile, highest values 4th quartile)

[lawi227]

I am currently using this formula to assign values a quartile in column AI. The problem is that it ranks the GREATEST values with a Quartile "1" and I want the LOWEST values with a Quartile "1".

=MATCH(AI4,QUARTILE(AI$3:AI$67,{4,3,2,1}),-1)

I tried using the following equation but it returns an occasional blank:
=MATCH(AI4,QUARTILE(AI$3:AI$67,{1,2,3,4}),1)

Truthfully, I'm not entirely sure how the above equation works, but I tried to do the inverse.

 

[AliGW]

I don't fully understand it either, but this should do what you want:

=CHOOSE(MATCH(D1,QUARTILE($D$1:$D$37,{4,3,2,1}),-1),4,3,2,1)

 

[Snaybot]

Just do a simple if then statement

=IF(MATCH(AI4,QUARTILE(AI$3:AI$67,{4,3,2,1}),-1)=1,4,IF(MATCH(AI4,QUARTILE(AI$3:AI$67,{4,3,2,1}),-1)=2,3,IF(MATCH(AI4,QUARTILE(AI$3:AI$67,{4,3,2,1}),-1)=3,2,IF(MATCH(AI4,QUARTILE(AI$3:AI$67,{4,3,2,1}),-1)=4,1,""))))

That should do it

 

[lawi227]

Thanks! This worked.

How did the choose function work?

 

[AliGW]

OK, so it works like this:

=CHOOSE(lookup_value,return_value_1,return_value_2,etc.)

It assumes that the lookup value will be a number between 1 and whatever, and it then looks at each of the return values given to determine which one to use - they are taken in order, with the first returned if the lookup value is 1, the second if the lookup value is 2 and so on. So here it looks at the quartile returned by your original formula, which is a number between 1 and 4, and returns 4 if it's 1, 3 if it's 2, 2 if it's 3 and 1 if it's 4.

Hope that makes sense!

 

[AhoyNC]

You might also try:
=LOOKUP(PERCENTRANK($AI$3:$AI$67,AI4),{0,0.25,0.5,0.75},{1,2,3,4})

<tbody>
</tbody>