# grouping data inside an IF statement

#### sandors13

##### New Member
=IF(J5>0.75,"5",IF(J5>0.6,"4",IF(J5>0.45,"3",IF(J5>0.3,"2",IF(J5>0.15,"1","0")))))

I have this formula but the data keeps changing making my scale irrelevant or not sensitive enough.

Instead of always changing the scale is there a way to rank J5 (1-5) comparing it to the rest of the data in the column.

For example look at the whole column, group into Quintiles and then assign a 1thru 5. This way it is always using its peers group to grade instead of an arbitrary scale?

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### J03xcel

##### New Member
You could use the Rank formula:

 A B C 1 Formula List Rank 2 =6-RANK(B2,\$B\$2:\$B\$6) 1 1 3 =6-RANK(B3,\$B\$2:\$B\$6) 2 2 4 =6-RANK(B4,\$B\$2:\$B\$6) 3 3 5 =6-RANK(B5,\$B\$2:\$B\$6) 4 4 6 =6-RANK(B6,\$B\$2:\$B\$6) 5 5

<tbody>
</tbody>

If you wanted it ranked largest to smallest, simply remove the "6-" portion.

Hope this helps!

#### sandors13

##### New Member
But I have way more then 5 data points, and I need it to return a rank on a scale 1-5. I want to divide the data into groups (Quintiles) then ask IF in the top qunintile return "5". Iff in the secound quintile retun "4" ect.

#### sandors13

##### New Member
no because it is only one of many columns I rank.

#### J03xcel

##### New Member
Code:
``=IF(J5>MAX(A1:A20)*0.8,"5",IF(J5>MAX(A1:A20)*0.6,"4",IF(J5>MAX(A1:A20)*0.4,"3",IF(J5>MAX(A1:A20)*0.2,"2",IF(J5>0,"1","0")))))``

Substitute A1:A20 with your column of data.

#### sandors13

##### New Member
Thanks thats very helpful! Looks good I will give it a try right now

Replies
4
Views
126
Replies
10
Views
320
Replies
3
Views
474
Replies
1
Views
370
Replies
9
Views
343

1,172,166
Messages
5,879,423
Members
433,429
Latest member
Ever

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

### Which adblocker are you using?

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

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