grouping data inside an IF statement

[sandors13]

=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?

 

[J03xcel]

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]

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.

 

[ChrisM]

is your data already sorted lowest to highest?

 

[sandors13]

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

 

[J03xcel]

=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]

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