Nested IF with OR conditions

  • Thread starter Thread starter Eva Shanley
  • Start date Start date
E

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.
 
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,"")))
 
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
 
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.
Click to expand...

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
 
Back
Top