+ Reply to Thread
Results 1 to 8 of 8

Nesting more than 7 If Statements, need help

  1. 10-24-2008, 10:04 AM #1
    Padgoi
    Padgoi is offline
    Registered User
    Join Date
    10-24-2008
    Location
    New York
    Posts
    10

    Nesting more than 7 If Statements, need help

    Hi, I am trying to use a formula, but the formula I am using is nesting more than 7 IF Statements together, which Excel does not allow. Can anyone help me out and offer a solution to the problem? I know cutting the formula in half and naming them should work, but it doesn't seem to be.

    Here's the entire formula I need:

    =IF(D93<1,0,IF(F93<1,C12,IF(H93<1,E12+C12,IF(J93<1,G12+E12+C12,IF(L93<1,I12+G12+E12+C12,IF(N93<1,K12+I12+G12+E12+C12,IF(P93<1,M12+K12+I12+G12+E12+C12,IF(R93<1,(O12+M12+K12+I12+G12+E12+C12),IF(T93<1,(Q12+O12+M12+K12+I12+G12+E12+C12),IF(V93<1,(S12+Q12+O12+M12+K12+I12+G12+E12+C12),IF(X93<1,(U12+S12+Q12+O12+M12+K12+I12+G12+E12+C12),IF(Z93<1,(W12+U12+S12+Q12+O12+M12+K12+I12+G12+E12+C12)0))))))))))))
    Reply With QuoteRegister To Reply
  2. 10-24-2008, 10:07 AM #2
    arthurbr
    arthurbr is offline
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    Hi and welcome to the board,
    this much asked question has been answered often. Try the forum's search function, it should yield answers
    Reply With QuoteRegister To Reply
  3. 10-24-2008, 10:12 AM #3
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    one way is to split it
    in a1
    Please Login or Register  to view this content.
    in a2
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 10-24-2008, 10:19 AM #4
    Padgoi
    Padgoi is offline
    Registered User
    Join Date
    10-24-2008
    Location
    New York
    Posts
    10
    arthur, I did a search and nothing I see helped, most of them used the Index function, which I don't think I can use in this situation.

    Martin, after I do that, what's the next step to put them together?
    Reply With QuoteRegister To Reply
  5. 10-24-2008, 10:40 AM #5
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    What are in the "in-between" columns in Row 93...

    If they are just text strings, then a possible formula could be something like:

    =CHOOSE(MATCH(TRUE,D93<Z93,0),0,C12,(E12+C12),(G12+E12+C12),...etc)

    You are allowed up to 30 choices....

    The formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER for it to work properly. You will see { } brackets appear.
    Last edited by NBVC; 10-24-2008 at 11:54 AM.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  6. 10-24-2008, 11:20 AM #6
    Ron Coderre
    Ron Coderre is offline
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Summing a conditional range of alternate values

    This ARRAY FORMULA (committed with CTRL+SHIFT+ENTER, instead of
    just ENTER) seems to work....
    Please Login or Register  to view this content.
    Note: If there is text beteen the Row_12 values, an error results.

    Does that help?
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules
    Reply With QuoteRegister To Reply
  7. 10-24-2008, 11:24 AM #7
    Padgoi
    Padgoi is offline
    Registered User
    Join Date
    10-24-2008
    Location
    New York
    Posts
    10
    I used Martin's advice and split the formula into two in other columns, then used a simple IF function where I need the result to show and it worked perfectly.

    Thanks for the help guys, much obliged.
    Reply With QuoteRegister To Reply
  8. 10-24-2008, 11:55 AM #8
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Here's another formula that should do it without have to write out all those sum options:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1