Complex If

[PAL]

Any ideas where I went wrong here. Can't leave the cell

=IF(AND(U2="YES",OR(E2="Not-applicable",E2="Red"),"Yes",""))

 

[Dave Peterson]

Misplaced ) paren:

=IF(AND(U2="YES",OR(E2="Not-applicable",E2="Red")),"Yes","")

 

[Max]

Try: =IF(AND(U2="YES",OR(E2="Not-applicable",E2="Red")),"Yes","")
It was just a parens thingy. High five? hit the YES below

 

[PAL]

I had to change it around a bit.

=IF(AND(U2="YES",OR(E2<>"Not-applicable",E2<>"Red")),"Yes","").

It seems to only be recognizing the U2, piece not the OR. Ideas.

 

[Max]

As-is, I don't see anything wrong with your expressions. If its not working
as expected, it could be your data quality, ie the text contains extraneous
white spaces throwing things off. Try wrapping TRIM around U2 and E2, eg:
=IF(AND(TRIM(U2)="YES",OR(TRIM(E2)<>"Not-applicable",TRIM(E2)<>"Red")),"Yes","")

 

[Rick Rothstein]

The OR condition, as written, will be met by any value in E2. Think about
it... any value in E2 that is not "Not-applicable" will meet the first
condition and, since this is an OR, the OR function will evaluate to TRUE.
On the other hand, if E2 does equal "Not-applicable", then the second part
of the OR condition will be true and, again, since this is an OR, the OR
function will evaluate to TRUE.

I'm kind of thinking your first expression is what you wanted, just
straighten out the parentheses as Dave and Max showed you.

 

[Rick Rothstein]

I didn't explain that exactly right. Let me try again. If E2 is equal to the
value "Not-applicable", then it will the second condition true (that is, E2
will not be equal to "Red") and hence, being an OR test, the OR function
will evaluate to TRUE. If E2 is equal to any other value, then the first
condition will be true and, again since this is an OR test, the OR function
will evaluate to TRUE. So, no matter what is in E2, one of the two "not
equal" tests will be true and the OR function will evaluate to TRUE... this
is no value that you can put into E2 to make both of those test come out
FALSE (which is the only way the OR function can return a FALSE value).