Should this be an IF AND statement or not?

[Ajay72]

So I'm working on a spreadsheet and have written an IF AND statement which works 50% of the time. I'm not sure if I should be using If AND or if something else is more appropriate.

I have two cells that each have two options from a drop down list. What I want is "if A1 = x and A2 = y then enter value at A3, if A1 = x and A2 = z then enter value at B3, if A1 = c and A2 = y then enter value at C3 if none of these criteria are met then enter value at D3"

What I have written:

=IF(AND($C$3="16:9",$E$4="wide"),D22,IF(AND($C$4="16:9",$E$4="diagonal"),D19,IF(AND($C$3="2.35:1",$E$4="wide"),D23,D20)))

This works for criteria 1 (16:9, Wide) and criteria 3 (2.35:1, wide) but return the false D20 for criteria 2 and for a false statement.

Any help very much appreciated

 

[kidwispa]

So I'm working on a spreadsheet and have written an IF AND statement which works 50% of the time. I'm not sure if I should be using If AND or if something else is more appropriate.

I have two cells that each have two options from a drop down list. What I want is "if A1 = x and A2 = y then enter value at A3, if A1 = x and A2 = z then enter value at B3, if A1 = c and A2 = y then enter value at C3 if none of these criteria are met then enter value at D3"

What I have written:

=IF(AND($C$3="16:9",$E$4="wide"),D22,IF(AND($C$4="16:9",$E$4="diagonal"),D19,IF(AND($C$3="2.35:1",$E$4="wide"),D23,D20)))

This works for criteria 1 (16:9, Wide) and criteria 3 (2.35:1, wide) but return the false D20 for criteria 2 and for a false statement.

Any help very much appreciated

It appears that you should be referencing $C$3 instead of $C$4 for the second if statement....

 

[Ajay72]

Correct! But that has not changed the outcome. Criteria 2 still returns the false statement.

 

[kidwispa]

Correct! But that has not changed the outcome. Criteria 2 still returns the false statement.

I would use the Evaluate Formula option in excel... this should help you to see where your formula is going wrong...

just as a guess though, if the "16:9" part is working (as if it didn't the other options would return FALSE), then double check your spelling of "diagonal", both in the formula and in cell E4

 

[Ajay72]

Wow, not seen the evaluator before, that's a helpful tool! All sorted now thanks, embarrassed to say it was a schoolboy error!

 

[kidwispa]

Wow, not seen the evaluator before, that's a helpful tool! All sorted now thanks, embarrassed to say it was a schoolboy error!

No worries - glad to help