Nested IF with OR conditions

[Eva Shanley]

One of the users here was having problems with a nested IF
that included AND conditions; I ended up doing a VLOOKUP
for her. I couldn't get her formula to work either, but
suspect I didn't have a paren or something in the right
place, and I'd really like to know the correct way to do
this. Her criteria was:
IF A1 > 1 AND A1 < 3 Then 1
IF A1 > 3 AND A1 < 7 Then 2
IF A1 > >7 AND A1< 14 Then 3
Else ""

Can anyone show me how this formula would be done? TIA.

 

[Frank Kabel]

Hi
try
=IF(AND(A1>1,A1<3),1,IF(AND(A1>3,A1<7),2,IF(AND(A1>7,A1<14),3,"")))

Note: This formula will return "" for 1, 3, 7, 14 based on your
conditions (e.g. 3 is not defined). Not sure if this is the desired
result.

Maybe the following formulas would come closer:
=IF(AND(A1>=1,A1<3),1,IF(AND(A1>=3,A1<7),2,IF(AND(A1>=7,A1<14),3,"")))

 

[Bernie Deitrick]

Eva,

=IF(AND(A1>1,A1<3),1,IF(AND(A1>3,A1<7),2,IF(AND(A1>7,A1<14),3,"")))

Though I suspect you would need to handle the cases for when A1 exactly
equals 3, etc.

You could also change your logic slightly to tighten this up and handle
exact values:

=IF(A1<1,"",IF(A1<3,1,IF(A1<7,2,IF(A1<14,3,""))))

HTH,
Bernie
MS Excel MVP

 

[Eva Shanley]

Thanks to both Bernie and Frank for their solutions to my
problem!

 

[Ron Rosenfeld]

One of the users here was having problems with a nested IF
that included AND conditions; I ended up doing a VLOOKUP
for her. I couldn't get her formula to work either, but
suspect I didn't have a paren or something in the right
place, and I'd really like to know the correct way to do
this. Her criteria was:
IF A1 > 1 AND A1 < 3 Then 1
IF A1 > 3 AND A1 < 7 Then 2
IF A1 > >7 AND A1< 14 Then 3
Else ""

Can anyone show me how this formula would be done? TIA.

I hope you realize that your specification will return "" if A1 = 1 or 3 or 7
or 14.

However, if that is not really what you want, perhaps:

=IF(AND(A1>=1,A1<=14),MATCH(A1,{1,3,7}),"")

might do the trick.


--ron