IF Statement for a negative range

[amanlalwani]

Score Range

5	-30%
4	-10% to -29%
3	+10% to -9%
2	+11% to +29%
1	>30%;High

Hello,
I need to test the above mentioned criteria for a set of values(in percent). Could someone help me in putting this into a formula using IF/And or anything else.

Thank you!!!

 

[omairhe]

you can try this...

A1 =IF(B1<-29%,5,IF(B1<-9%,4,IF(B1<11%,3,IF(B1<30%,2,IF(B1>29%,1)))))

 

[snjpverma]

Or this:

=LOOKUP(B1,{-0.3;-0.29;-0.09;0.11;0.31},{5;4;3;2;1})