Nested IF, AND, OR (2003)

Topic

New Reply

What I would like to do:

Read the value in Cell K19.

IF that value (in K19) = “Death or Life Threatening”
AND Cell ‘New Case’!K52 OR ‘New Case’!K53 = Yes, the true statement should be “OK”

If that value (in K19) = “—-”
AND Cell ‘TC Scenario’!K52 OR ‘TC Scenario’!K53 = “—-” OR “No”, the true statement should be “OK”

False = “Error”

Reply | Quote

Replies

=IF(OR(AND(K19=”Death or Life Threatening”,’New Case’!K52=”Yes”),AND(K19=”—-“,OR(‘TC Scenario’!K52=”—-“,’TC Scenario’!K53=”—-“))),”OK”,”Error”)

Reply | Quote

Thank you _again_ Hans!

I seem to get really twisted up when figuring out where the ANDs/ORs go.

Reply | Quote

I build such a formula in steps, not all at once.

First step: the IF function

=IF(…,”OK”,”Error”)

Second step: there are two main situations, so the … are replaced with an OR function:

=IF(OR(…,…),”OK”,”Error”)

Both … involve 2 conditions that should be satisfied, so they are replaced with AND functions:

=IF(OR(AND(…,…),AND(…,…)),”OK”,”Error”)

The first AND is straightforward, but the second one involves another OR in the second part:

=IF(OR(AND(…,…),AND(…,OR(…,…))),”OK”,”Error”)

Then it’s “just” a matter of reading the specifications carefully and filling in the dots.

Reply | Quote

VERY Helpful!!! Thank You!
UPDATE: The above tutorial is _wonderful_. Based on your methodology, I was able to create a formula with almost twice the criteria.

I left out a couple of the criteria AND had an incorrect sheet reference… my final formula was:

=IF(OR(AND(E$19=”Death or Life Threatening”, OR(‘New Case’!E$52=”—-“,’New Case’!E$52=”Yes”,’New Case’!E$53=”—-“,’New Case’!E$53=”Yes”)),AND(E$19=”—-“,OR(‘New Case’!E$52=”—-“,’New Case’!E$52=”No”,’New Case’!E$53=”—-“,’New Case’!E$53=”No”))),”OK”,”Error”)

Reply | Quote

I build such a formula in steps, not all at once.

First step: the IF function

=IF(…,”OK”,”Error”)

Second step: there are two main situations, so the … are replaced with an OR function:

=IF(OR(…,…),”OK”,”Error”)

Both … involve 2 conditions that should be satisfied, so they are replaced with AND functions:

=IF(OR(AND(…,…),AND(…,…)),”OK”,”Error”)

My first post in the Lounge.

I was looking for an answer to how to do these complex conditions using Excel’s IF function. I was doing what I thought was the obvious thing:

=IF (condition1 AND condition2, “It’s true”, “It’s false”)

and kept getting a syntax error. I eventually became convinced (incorrectly) that the IF function supports only simple conditions and that even to do simply things, I had to do ridiculous workarounds using nested IF’s:

=IF(X>0, IF (Y>0, “It’s true!”, “It’s false”), “It’s false”)

Your post made it clear to me that AND and OR in Excel are *functions* rather than *operators*. Bing!

Thanks so much for the excellent answer to the OP question.

Now I see why everyone loves the Lounge so much. 😉

Cheers!

Reply | Quote

Welcome to the Lounge!

Thanks for your kind words, I’m glad the reply was useful to you.

Reply | Quote