Making IF Statement combining AND and OR

[misk0048]

Hello,

I'm trying to make an if statement where if one cell equals "BZ" and another cell doesn't equal one of several values then a 1 is displayed.

My current attempt is not working: =IF(OR(LEFT($O2,4)<>"0940",LEFT($O2,4)<>"0950",LEFT($O2,4)<>"0740",AND(AB$1=$Q2)),1,"")

Any ideas?

 

[wideboydixon]

=if(and($q2=ab$1,or(left($o2,4)<>"0940",left($o2,4)<>"0950",left($o2,4)<>"0740")),1,"")

wbd

 

[Jonmo1]

I think that should all be a single AND.

Consider this example
OR(LEFT($O2,4)<>"0940",LEFT($O2,4)<>"0950")
That will ALWAYS be TRUE. No matter what value you have in O2 (except an error value like #N/A)
If LEFT($O2,4) DOES happen to be 0940, then LEFT($O2,4)<>"0950" is TRUE, so the OR is TRUE.
If LEFT($O2,4) DOES happen to be 0950, then LEFT($O2,4)<>"0940" is TRUE, so the OR is TRUE.

Try
=IF(AND(LEFT($O2,4)<>"0940",LEFT($O2,4)<>"0950",LEFT($O2,4)<>"0740",AB$1=$Q2),1,"")

 

[Dave Patton]

try
=(AND(OR(LEFT($O2,4)<>"0940",LEFT($O2,4)<>"0950",LEFT($O2,4)<>"0740"),AB$1=$Q2))+0

 

[wideboydixon]

I think that should all be a single AND.

Consider this example
OR(LEFT($O2,4)<>"0940",LEFT($O2,4)<>"0950")
That will ALWAYS be TRUE. No matter what value you have in O2 (except an error value like #N/A)
If LEFT($O2,4) DOES happen to be 0940, then LEFT($O2,4)<>"0950" is TRUE, so the OR is TRUE.
If LEFT($O2,4) DOES happen to be 0950, then LEFT($O2,4)<>"0940" is TRUE, so the OR is TRUE.

Try
=IF(AND(LEFT($O2,4)<>"0940",LEFT($O2,4)<>"0950",LEFT($O2,4)<>"0740",AB$1=$Q2),1,"")

Doh! Good spot!!

WBD

 

[misk0048]

Thanks WBD for your reply.

For some reason your formula is still giving me a 1 for cells that have BZ and those values. I have rechecked the cells the formulas are referring to and the one says BZ and the other does start with those values (eg: 0950) so I'm not sure where/why the formula is failing.

 

[misk0048]

Awesome, this appears to work! Many thanks!

I think that should all be a single AND.

Consider this example
OR(LEFT($O2,4)<>"0940",LEFT($O2,4)<>"0950")
That will ALWAYS be TRUE. No matter what value you have in O2 (except an error value like #N/A)
If LEFT($O2,4) DOES happen to be 0940, then LEFT($O2,4)<>"0950" is TRUE, so the OR is TRUE.
If LEFT($O2,4) DOES happen to be 0950, then LEFT($O2,4)<>"0940" is TRUE, so the OR is TRUE.

Try
=IF(AND(LEFT($O2,4)<>"0940",LEFT($O2,4)<>"0950",LEFT($O2,4)<>"0740",AB$1=$Q2),1,"")

 

[Jonmo1]

You're wecome.

You can use a little shortcut on that AND

=IF(AND(LEFT($O2,4)<>{"0940","0950","0740"},AB$1=$Q2),1,"")