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

#### lawi227

##### Board Regular
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.

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### AliGW

##### Banned
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

##### New Member
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

##### Board Regular
Thanks! This worked.

How did the choose function work?

#### AliGW

##### Banned
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

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

<tbody>
</tbody>

Last edited:

Replies
3
Views
209
Replies
4
Views
249
Replies
7
Views
200
Replies
3
Views
2K
Replies
3
Views
563

Threads
1,190,811
Messages
5,983,049
Members
439,817
Latest member
jessicabrown

### 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

### 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