Multi level ifs function
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Multi level ifs function
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2013
    Posts
    124
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Multi level ifs function

    I'm working in Excel 365 and I have a multi level if function that I'm having trouble figuring out.

    I'm not sure how to write the formula.

    I want to add:
    IF G12 is blank and G13 is blank use AA7
    IF G12 not blank and G13 is blank use AA8
    IF G12 is blank and G13 is not blank use AA8
    IF G12 not blank and G13 not blank use AA9

    to this formula:
    =IF(AND(CopyRME!$AA9="REPORT",CopyRME!$Z9="Within Limits"),CopyRME!$R9,"")

    I need Z9 and R9 to change according to the Row # as well. So the first cell ref above would be AA7 Z7 and R7 and so on depending on the G12 and G13 contents.

    Much appreciated any help.

    ohmedic88
    G

  2. #2
    Board Regular
    Join Date
    Nov 2014
    Location
    South Africa
    Posts
    241
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi level ifs function

    Hi there,
    Give this one a try.

    Code:
    =IF(AND(ISBLANK(G12),ISBLANK(G13)),AA7,IF(OR(AND(G12<>"",ISBLANK(G13)),AND(G12<>"",ISBLANK(G13))),AA8,IF(AND(G12<>"",G12<>""),AA9)))

  3. #3
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,520
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Multi level ifs function

    You could treat booleans as integers.

    =CHOOSE(1+(G12="")+(G13=""), $AA$7, $AA$8, $AA$9)

  4. #4
    Board Regular
    Join Date
    Jun 2013
    Posts
    124
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi level ifs function

    I didn't get this one to work properly. The test I ran returned the value in AA9, but I need the value in R8. Maybe I'm confused do I need to add my original formula to your calculation?

  5. #5
    Board Regular
    Join Date
    Jun 2013
    Posts
    124
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi level ifs function

    I haven't yet implemented your solution, but I'm confused. Do I also need to add my formula to part of your calculation.

    Eric

  6. #6
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,520
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Multi level ifs function

    My formula should return this part of the OP
    IF G12 is blank and G13 is blank use AA7
    IF G12 not blank and G13 is blank use AA8
    IF G12 is blank and G13 is not blank use AA8
    IF G12 not blank and G13 not blank use AA9
    Its not clear what you want about R9

  7. #7
    Board Regular
    Join Date
    Jun 2013
    Posts
    124
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi level ifs function

    Quote Originally Posted by mikerickson View Post
    My formula should return this part of the OP


    Its not clear what you want about R9

    I am trying to align with a row of data on the CopyRME tab. It is the formula at the bottom of my original comment.
    =IF(AND(CopyRME!$AA9="REPORT",CopyRME!$Z9="Within Limits"),CopyRME!$R9,"")
    So if G12 and G13 is blank I need to align data to AA7 Z7 and R7 values
    So if G12 and G13 is not blank I need to align data to AA9 Z9 and R9 values
    If 1 is blank and the other is not I need to align data to AA8 Z8 and R8

    I'm not sure how to write that code. So it will adjust to the correct result.

    Eric

  8. #8
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,520
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Multi level ifs function

    What does "align data" mean?

  9. #9
    Board Regular
    Join Date
    Jun 2013
    Posts
    124
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi level ifs function

    I need to the Row number to change so it finds the correct data in the correct location of the report that I have.

  10. #10
    Board Regular
    Join Date
    Jun 2013
    Posts
    124
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi level ifs function

    Ok I finaly was able to research the choose formula and I just put my if formula in the aa7, aa8 and aa9. makes for a long formula but I'm gonna try to test it out today.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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