IF AND formula

[sadsfan]

Hi I am using Excel 2013 and I want to use an IF AND statement to check the value of 1 cell and another.
So I am checking:
if B1=1 AND B2=100 is true I want to return the value "2" if B1=1 AND B2<100 I want to return the value "1"
if B1=2 AND B2=100 is true I want to return the value "3" if B1=2 AND B2<100 I want to return the value "2"
How can I do this? I am struggling with trying to write a formula and think I may need to use vba, can anyone help?

 

[Joe4]

Try:

=IF(AND(B1=2,B2=100),3,IF(OR(AND(B1=2,B2 < 100),AND(B1=1,B2=100)),2,IF(AND(B1=1,B2 < 100),1,"")))

 

[etaf]

return the value in another cell
if not then VBA

if B1=1 AND B2=100 is true I want to return the value "2" if B1=1 AND B2 < 100 I want to return the value "1"
if B1=2 AND B2=100 is true I want to return the value "3" if B1=2 AND B2 < 100 I want to return the value "2"


if B1=1 AND B2=100 is true I want to return the value "2"
if B1=2 AND B2 < 100 I want to return the value "2"

So thats an OR and AND

AND(B1=1, B2=100)
AND(B1=2, B2 < 100)
if either of those are true - you want a 2

OR(AND(B1=1, B2=100), AND(B1=2, B2 < 100))
now in the
IF
=IF( OR(AND(B1=1, B2=100), AND(B1=2, B2 < 100)) , 2,
now we to add a nested IF for 1 and 3
B1=1 AND B2<100 I want to return the value "1"

=AND( B1 =1 , B2 < 100 )
=IF( OR(AND(B1=1, B2=100), AND(B1=2, B2 < 100)) , 2, IF ( AND( B1 =1 , B2 < 100 ) , 1,

then
B1=2 AND B2=100 is true I want to return the value "3"
=AND( B1 =2 , B2 =100 )

=IF( OR(AND(B1=1, B2=100), AND(B1=2, B2 < 100)) , 2, IF ( AND( B1 =1 , B2 < 100 ) , 1, IF ( AND( B1 =2 , B2 =100 ) , 3 , "criteria not met" )))

 

[sadsfan]

Try:

=IF(AND(B1=2,B2=100),3,IF(OR(AND(B1=2,B2 < 100),AND(B1=1,B2=100)),2,IF(AND(B1=1,B2 < 100),1,"")))

Thanks Joe4, that worked perfectly and I can see where I was going wrong.

 

[sadsfan]

return the value in another cell
if not then VBA

if B1=1 AND B2=100 is true I want to return the value "2" if B1=1 AND B2 < 100 I want to return the value "1"
if B1=2 AND B2=100 is true I want to return the value "3" if B1=2 AND B2 < 100 I want to return the value "2"


if B1=1 AND B2=100 is true I want to return the value "2"
if B1=2 AND B2 < 100 I want to return the value "2"

So thats an OR and AND

AND(B1=1, B2=100)
AND(B1=2, B2 < 100)
if either of those are true - you want a 2

OR(AND(B1=1, B2=100), AND(B1=2, B2 < 100))
now in the
IF
=IF( OR(AND(B1=1, B2=100), AND(B1=2, B2 < 100)) , 2,
now we to add a nested IF for 1 and 3
B1=1 AND B2<100 I want to return the value "1"

=AND( B1 =1 , B2 < 100 )
=IF( OR(AND(B1=1, B2=100), AND(B1=2, B2 < 100)) , 2, IF ( AND( B1 =1 , B2 < 100 ) , 1,

then
B1=2 AND B2=100 is true I want to return the value "3"
=AND( B1 =2 , B2 =100 )

=IF( OR(AND(B1=1, B2=100), AND(B1=2, B2 < 100)) , 2, IF ( AND( B1 =1 , B2 < 100 ) , 1, IF ( AND( B1 =2 , B2 =100 ) , 3 , "criteria not met" )))

Thanks Etaf, if I have any trouble with Joe4's formula, I'll use this code.

 

[etaf]

its the same formula as joe4