Results 1 to 6 of 6
  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 with lots of ORs (2003)

    I'm trying to create a formula for the following:

    IF K15 = "Spontaneous" and 'New Case'!K5="SP" OR "L" OR "HA" OR "SR" OR "SH","OK","Error"
    OR
    IF K15 = "Literature (Spon)" and 'New Case'!K5="L","OK","Error"
    OR
    IF K15 = "Spontaneous from HA" and 'New Case'!K5="HA","OK","Error"
    OR
    IF K15 = "Stimulated (retro/Disease)" and 'New Case'!K5="SR","OK","Error"
    OR
    IF K15 = "Not Available" and 'New Case'!K5="NA","OK","Error"
    OR
    IF K15 = "Clinical Trial" and 'New Case'!K5="CS" OR "CU","OK","Error"

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nested IF/AND with lots of ORs (2003)

    Try

    =IF(OR(AND(K15="Spontaneous",OR('New Case'!K5="SP",'New Case'!K5="L",'New Case'!K5="HA",'New Case'!K5="SR",'New Case'!K5="SH")),AND(K15="Literature (Spon)",'New Case'!K5="L"),AND(K15="Spontaneous from HA",'New Case'!K5="HA"),AND(K15="Stimulated (retro/Disease)",'New Case'!K5="SR"),AND(K15="Not Available",'New Case'!K5="NA"),AND(K15="Clinical Trial",OR('New Case'!K5="CS",'New Case'!K5="CU"))),"OK","Error")

    (This will be displayed on several lines, but it is one formula)

    <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20>

  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 with lots of ORs (2003)

    Wow, that had my head spinning!

    Perfect.

    Now to disect/analyse so that I can apply it to others...

    <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Nested IF/AND with lots of ORs (2003)

    Hi musical,

    Here's another two renditions, both slightly shorter:
    =IF((K15="Spontaneous")*NOT(ISERROR(MATCH('New Case'!K5,{"SP","L","HA","SR","SH"},0)))+(K15="Lite rature (Spon)")*('New Case'!K5="L")+(K15="Spontaneous from HA")*('New Case'!K5="HA")+(K15="Stimulated (retro/Disease)")*('New Case'!K5="SR")+(K15="Not Available")*('New Case'!K5="NA")+(K15="Clinical Trial")*NOT(ISERROR(MATCH('New Case'!K5,{"CS","CU"},0)))>0,"OK","Error")

    =IF(OR(AND(K15="Spontaneous",NOT(ISERROR(MATCH('Ne w Case'!K5,{"SP","L","HA","SR","SH"},0)))),AND(K15=" Literature (Spon)",'New Case'!K5="L"),AND(K15="Spontaneous from HA",'New Case'!K5="HA"),AND(K15="Stimulated (retro/Disease)",'New Case'!K5="SR"),AND(K15="Not Available",'New Case'!K5="NA"),AND(K15="Clinical Trial",NOT(ISERROR(MATCH('New Case'!K5,{"CS","CU"},0))))),"OK","Error")

    Both renditions have array matches (eg MATCH('New Case'!K5,{"SP","L","HA","SR","SH"},0)) where you have multiple criteria, which make it easier to add/delete criteria as the need arises. You'll probably find the second version slightly easier to digest - the first one replaces the AND & OR expressions with * and +, respectively.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  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 with lots of ORs (2003)

    When I first saw your post, I thought to myself... "beginner"... stick to the basics and what you almost know.

    Then, wouldn't you know, I maxed out the space avaliable in the cell (formula too long).

    Ok..."what was that about arrays?"

    In parts of the formula, I have NO clue what it was "doing" (example, the ',0' at the end of each phrase), but plagerizim at it's best, I created the following (and got it correct on the first try):

    =IF(OR(AND(E15="Clinical Trial",E18="Associated",Events!E20="Y",Events!E22= "Y"),
    AND(E15="Clinical Literature",E18="Associated",Events!E20="Y",Events !E22="Y"),
    AND(E15="Clinical Trial",E18="Not Associated",Events!E20="N",OR(Events!E22="N",Event s!E22="NRP")),
    AND(E15="Clinical Literature",E18="Not Associated",Events!E20="N",Events!E22="N"),
    AND(E15="Clinical Literature",E18="----",NOT(ISERROR(MATCH(Events!E22,{"Y","N","NRP","UNK "},0)))),
    AND(E15="Spontaneous",NOT(ISERROR(MATCH(Events!E20 ,{"PRO","POS","UNL","UNC"},0)))),
    AND(E15="Literature (Spon)", NOT(ISERROR(MATCH(Events!E22,{"Y","N","NRP","UNK"} ,0)))),
    AND(E15="Spontaneous from HA", NOT(ISERROR(MATCH(Events!E22,{"Y","N","NRP","UNK"} ,0)))),
    AND(E15="Stimulated (retro/Disease)", NOT(ISERROR(MATCH(Events!E22,{"Y","N","NRP","UNK"} ,0)))),
    AND(E15="Not Available", NOT(ISERROR(MATCH(Events!E22,{"Y","N","NRP","UNK"} ,0))))),"OK","Error")



    Many, many thanks,
    Jody

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Nested IF/AND with lots of ORs (2003)

    Hi Jody,

    Congratulations!

    The ',0' at the end of each MATCH function tells that function to use 'exact' matching; otherwise, false matches might be returned. The MATCH function supports 'exact', 'nearest equal to or above' and 'nearest equal to or less' matching. See Excel's help file for more details. With 'exact' matching, the MATCH function will return an error value (#N/A!) if no match is found. Using the ISERROR function tests whether the MATCH function returned an error (1=error, 0 = no error) and the NOT function then reverses the ISERROR results (because we want to use the 'true' matches).
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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