Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    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. #6
    New Lounger
    Join Date
    Mar 2009
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post

    [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. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [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
  •