Ifs Statement with inequalities

[WashingtonDC]

Can I write an Ifs statement that contains several inequalities? Below are the criteria:

If Cell >=15% then value= 1
If cell is between 6%-14% then value= 2
If cell is between -5% and 5% then value = 3
If cell is between -6% and -15% then value = 2
If cell is less than -15% then value = 1

 

[AlanY]

try, with cell in A1

=IF(OR(A1<-0.15,A1>=0.15),1,IF(AND(A1>=0.05,A1<=-0.05),3,2))

 

[Joe4]

Since the positive and negative values are mirror images of each other, you can simplify it a bit by taking advantage of the Absolute Value function (ABS), i.e.

=IF(ABS(A1)>=0.15,1,IF(ABS(A1)>=0.06,2,3))

 

[steve the fish]

Here is a way:

=IF(ISNUMBER(A1),IF(ABS(A1)>=0.15,1,IF(ABS(A1)>0.05,2,3)),"")

 

[joeu2004]

=IF(A1<-15%, 1, IF(A1<=-6%, 2, IF(A1<=5%, 3, IF(A1<15%, 2, 1))))

Note that if A1 is 5.1%, a condition that you do not cover, the formula returns 2. To avoid that, be sure that A1 is rounded to 0 percentage decimal places.