PDA

View Full Version : Solved: Combinging AND and OR in Nested If Statements



Opv
03-25-2013, 05:35 PM
=IF(OR(OR(AND(AND($C3<$AD$2,$G3<$AD$2),$W3=""),$Y3="")),1,0)


My objective is the following:

IF $C3 is less than $AD$2
OR $G3 is less than $AD$2
AND $W3 = ""
AND $Y3 = ""
THEN cell value = 1
ELSE cell value = 0

For some reason my formula is consistently returning a value of 1. Is there something about the order of the conditions in the formula that might be causing this?

psctornado
03-25-2013, 05:57 PM
Does this work for you?

=IF(OR(AND($C$3<$AD$3,$G$3<$AD$3),AND($W$3="",$Y$3="")),1,0)

Let me know...

Opv
03-25-2013, 06:02 PM
Does this work for you?

=IF(OR(AND($C$3<$AD$3,$G$3<$AD$3),AND($W$3="",$Y$3="")),1,0)

Let me know...

Thanks. I just realized that I had a superfluous And in my original formula; however, after removing it, the results was the same. Tried your version and it does just the opposite, returns a 0 in all cells. Something still appears to be off.

psctornado
03-25-2013, 06:18 PM
I see my formula refers to A3, rather than A2 as your example shows. I think that may be the only issue with my formula. If you think of the logic you explained earlier, the real important values should be in W3 & Y3. If either of those cells have a value then it turns on what is found in C3 & G3. Regardless of what is in both C3 & G3, provided that W3 & Y3 are "", then you should always get a 1.

Opv
03-25-2013, 06:18 PM
I just figured out why your formula wasn't working. You followed my lead and included the superfluous AND. Also, you used an absolute reference for all of the cell references. (Absolute reference is only needed for $AD$3.) Once I removed the absolute cell reference for the rows, it seems to be working.


=IF(OR($C3<$AD$2,$G3<$AD$2,AND($W3="",$Y3="")),1,0)

This seems to be working. Thanks for your help.

Opv
03-26-2013, 10:36 AM
Well, I spoke too quickly. Does the order of the operators make a difference? For example, is

=IF(AND($W4="",$Y4="",OR($C4<$AC$2,$G4<$AC$2)),1,0)

synonymous with?

=IF(OR($C4<$AC$2,$G4<$AC$2,AND($W4="",$Y4="")),1,0)