+ Reply to Thread
Results 1 to 4 of 4

nested if(and) functions

  1. #1
    Rohan
    Guest

    nested if(and) functions

    Hi,

    I need to create a function that can cope with more than 7 if ands, where it
    can read what's in cell A1 and what's in cell B1 then spit out a defined
    value in C1. For example,

    If A1="a" and B1="a", then 1, but if A1="a" and B1="b", then 2 and so on for
    combinations aa, ab, ac, ad, ae, ba, bb, bc..........ea, eb, ec, ed and ee.
    My formula below works but only gets me up to bc.

    =IF(AND(A1="a",B1="a"),"1",IF(AND(A1="a",B1="b"),"2",IF(AND(A1="a",B1="c"),"3",IF(AND(A1="a",B1="d"),"3",IF(AND(A1="b",B1="a"),"2",IF(AND(A1="b",B1="b"),"2",IF(AND(A1="b",B1="c"),"3",)))))))

    I've found some tricks to get around the 7-limit IF functions but can't seem
    to make them work for my nest IF(AND) function.

    I hope this makes sense. Any help would be most appreciated.

    thanks.

    Rohan.


  2. #2
    JE McGimpsey
    Guest

    Re: nested if(and) functions

    I would do something like:

    J K L M N
    1 a b c d
    2 a 1 2 3 3
    3 b 2 2 3 4
    4 c 3 3 4 4


    The use something like:

    =VLOOKUP(A1,J:N,MATCH(B1,J1:N1,FALSE),FALSE)




    In article <[email protected]>,
    "Rohan" <[email protected]> wrote:

    > Hi,
    >
    > I need to create a function that can cope with more than 7 if ands, where it
    > can read what's in cell A1 and what's in cell B1 then spit out a defined
    > value in C1. For example,
    >
    > If A1="a" and B1="a", then 1, but if A1="a" and B1="b", then 2 and so on for
    > combinations aa, ab, ac, ad, ae, ba, bb, bc..........ea, eb, ec, ed and ee.
    > My formula below works but only gets me up to bc.
    >
    > =IF(AND(A1="a",B1="a"),"1",IF(AND(A1="a",B1="b"),"2",IF(AND(A1="a",B1="c"),"3"
    > ,IF(AND(A1="a",B1="d"),"3",IF(AND(A1="b",B1="a"),"2",IF(AND(A1="b",B1="b"),"2"
    > ,IF(AND(A1="b",B1="c"),"3",)))))))
    >
    > I've found some tricks to get around the 7-limit IF functions but can't seem
    > to make them work for my nest IF(AND) function.
    >
    > I hope this makes sense. Any help would be most appreciated.
    >
    > thanks.
    >
    > Rohan.


  3. #3
    Biff
    Guest

    Re: nested if(and) functions

    Hi!

    Create a table that lists all the combinations and their corresponding
    values:

    AA 1
    AB 2
    AC 3
    AD 3
    BA 2
    BB 2
    BC 3

    Assume this table is in the range F1:G7

    Then use this formula in C1:

    =IF(ISNA(VLOOKUP(A1&B1,F1:G7,2,0)),"",VLOOKUP(A1&B1,F1:G7,2,0))

    Biff

    "Rohan" <[email protected]> wrote in message
    news:[email protected]...
    > Hi,
    >
    > I need to create a function that can cope with more than 7 if ands, where
    > it
    > can read what's in cell A1 and what's in cell B1 then spit out a defined
    > value in C1. For example,
    >
    > If A1="a" and B1="a", then 1, but if A1="a" and B1="b", then 2 and so on
    > for
    > combinations aa, ab, ac, ad, ae, ba, bb, bc..........ea, eb, ec, ed and
    > ee.
    > My formula below works but only gets me up to bc.
    >
    > =IF(AND(A1="a",B1="a"),"1",IF(AND(A1="a",B1="b"),"2",IF(AND(A1="a",B1="c"),"3",IF(AND(A1="a",B1="d"),"3",IF(AND(A1="b",B1="a"),"2",IF(AND(A1="b",B1="b"),"2",IF(AND(A1="b",B1="c"),"3",)))))))
    >
    > I've found some tricks to get around the 7-limit IF functions but can't
    > seem
    > to make them work for my nest IF(AND) function.
    >
    > I hope this makes sense. Any help would be most appreciated.
    >
    > thanks.
    >
    > Rohan.
    >




  4. #4
    Rohan
    Guest

    Re: nested if(and) functions

    beautiful. Thanks very much.

    "JE McGimpsey" wrote:

    > I would do something like:
    >
    > J K L M N
    > 1 a b c d
    > 2 a 1 2 3 3
    > 3 b 2 2 3 4
    > 4 c 3 3 4 4
    >
    >
    > The use something like:
    >
    > =VLOOKUP(A1,J:N,MATCH(B1,J1:N1,FALSE),FALSE)
    >
    >
    >
    >
    > In article <[email protected]>,
    > "Rohan" <[email protected]> wrote:
    >
    > > Hi,
    > >
    > > I need to create a function that can cope with more than 7 if ands, where it
    > > can read what's in cell A1 and what's in cell B1 then spit out a defined
    > > value in C1. For example,
    > >
    > > If A1="a" and B1="a", then 1, but if A1="a" and B1="b", then 2 and so on for
    > > combinations aa, ab, ac, ad, ae, ba, bb, bc..........ea, eb, ec, ed and ee.
    > > My formula below works but only gets me up to bc.
    > >
    > > =IF(AND(A1="a",B1="a"),"1",IF(AND(A1="a",B1="b"),"2",IF(AND(A1="a",B1="c"),"3"
    > > ,IF(AND(A1="a",B1="d"),"3",IF(AND(A1="b",B1="a"),"2",IF(AND(A1="b",B1="b"),"2"
    > > ,IF(AND(A1="b",B1="c"),"3",)))))))
    > >
    > > I've found some tricks to get around the 7-limit IF functions but can't seem
    > > to make them work for my nest IF(AND) function.
    > >
    > > I hope this makes sense. Any help would be most appreciated.
    > >
    > > thanks.
    > >
    > > Rohan.

    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1