# Thread: Nested IF, AND, OR (2003)

1. ## Nested IF, AND, OR (2003)

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"

2. ## Re: Nested IF, AND, OR (2003)

=IF(OR(AND(K19="Death or Life Threatening",'New Case'!K52="Yes"),AND(K19="----",OR('TC Scenario'!K52="----",'TC Scenario'!K53="----"))),"OK","Error")

3. ## Re: Nested IF, AND, OR (2003)

Thank you _again_ Hans!

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

4. ## Re: Nested IF, AND, OR (2003)

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","Er ror")

Then it's "just" a matter of reading the specifications carefully and filling in the dots.

5. ## Re: Nested IF, AND, OR (2003)

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")

6. [quote name='HansV' post='702972' date='27-Mar-2008 06:34']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")[/quote]

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!

7. [quote name='papayasoft' post='764977' date='12-Mar-2009 10:25'][/quote]
Welcome to the Lounge!

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•