IF/AND Statement with Multiple Conditions

[ACF0303]

Greetings,

I could certainly use some expert help with this formula. I am trying to accomplish the following:

If Cell H7 < 0, then put in "q"
If Cell H7 is greater than 0 and less than 1, put in "u"
If Cell H7 is greater than 1 and less than 5, put in "{"
If Cell H7 is greater than 5, put in "p"

What I came up with:

IF(H7<0,"q",IF(H7>0,AND(H7<1,"u",IF(H7>1,AND(H7<5,"{","p")))))e

The error I am getting indicates there is something wrong with the third IF statement. Any help would be greatly appreciated!

 

[ACF0303]

That last "e" after my formula is a typo.

 

[joeu2004]

I am trying to accomplish the following:
If Cell H7 < 0, then put in "q"
If Cell H7 is greater than 0 and less than 1, put in "u"
If Cell H7 is greater than 1 and less than 5, put in "{"
If Cell H7 is greater than 5, put in "p"

What I came up with:
IF(H7<0,"q",IF(H7>0,AND(H7<1,"u",IF(H7>1,AND(H7<5,"{","p")))))e

The error I am getting indicates there is something wrong with the third IF statement.

The correct syntax is:

=IF(H7<0,"q",IF(AND(H7>=0,H7<1),"u",IF(AND(H7>=1,H7<5),"{","p")))

But you are over-specifying the logic. Since Excel evaluates the IF() arguments left to right, you can write:

=IF(H7<0,"q",IF(H7<1,"u",IF(H7<5,"{","p")))

And be sure you truly want <0, <1 and <5. Note that the alternative to "less than 0" is not "greater than 0". Instead, it is "greater than or equal to 0". Or perhaps you meant to say "less than or equal to 0" and "greater than 0".

PS.... Alternatively, you could write:

=LOOKUP(H7,{-1E300,0,1,5},{"q","u","{","p"})

-1E300 is an arbitrary "large" negative number (i.e. large in magnitude).

 

[Fizzz]

This should work

=IF(H7>5,"p",IF(AND(H7>1,H7<5)=TRUE,"{",IF(AND(H7>0,H7<1)=TRUE,"u",IF(H7<0,"q"))))

 

[ACF0303]

Thanks for the quick response. I tried both suggested formulas and I am running into a different issue. Cell H7 = 2.12% and should return "{", however it is returning "u" I know it is probably something silly.....for example, should I be using % instead of whole numbers?

 

[Fizzz]

Thanks for the quick response. I tried both suggested formulas and I am running into a different issue. Cell H7 = 2.12% and should return "{", however it is returning "u" I know it is probably something silly.....for example, should I be using % instead of whole numbers?

2.12% is read by Excel as 0.0212, which falls into your category of If Cell H7 is greater than 0 and less than 1, put in "u".

If you are using percentages as your values in H7, all the values will give you "u", since they all go from 0 to 1 (or 0% to 100%)

 

[ACF0303]

I knew it was something silly. Thanks so much for the quick response (again). I will go have more coffee.

 

[Fizzz]

You can also adjust the values of your criterias to 1%, 5% and what not.

 

[ACF0303]

That's exactly what I did and now everything works perfectly! Thanks again.

 

[joeu2004]

[.... deleted ....]