grouping data inside an IF statement

sandors13

New Member
Joined
Jan 19, 2007
Messages
9
=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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

J03xcel

New Member
Joined
Dec 20, 2013
Messages
10
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
Joined
Jan 19, 2007
Messages
9
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
Joined
Jan 19, 2007
Messages
9

ADVERTISEMENT

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

J03xcel

New Member
Joined
Dec 20, 2013
Messages
10
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.
 

Forum statistics

Threads
1,136,353
Messages
5,675,283
Members
419,559
Latest member
BraytonM

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

Which adblocker are you using?

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
Top