Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Maxed out in nesting "if" statements

    It appears that I maxed out in an if formula with a total of 9 if statements. Any way around this dilemma. at this time I will need to test for several more criteria. What is the Max number one can use in this formula.

    Thanks

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    The limit is 7.

    The way around this depends on exactly what you are doing.

    One solution is to use a Vlookup, but that is not always applicable.
    Regards
    John



  3. #3
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts
    Check out Chip Pearson's site, he has a technique using named ranges that may help you HTH
    Paul Coyle
    Approach love and cooking with reckless abandon

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Another option is to create a user defined function {UDF} that does all the testing and returns the appropriate answer into the cell.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    5 Star Lounger RussB's Avatar
    Join Date
    Dec 2009
    Location
    Grand Rapids, Michigan
    Posts
    803
    Thanks
    10
    Thanked 50 Times in 49 Posts
    Or make the spreadsheet less complex and simpler for the next person and break it into two or more cells.
    Do you "Believe"? Do you vote? Please Read:
    LEARN something today so you can TEACH something tomorrow.
    DETAIL in your question promotes DETAIL in my answer.
    Dominus Vobiscum <))>(

  6. #6
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    What is a UDF user defined function and how will this help me accomplish my goal?

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    A function is something like Sum or Count, where the word is the name of a procedure. Sum and Count are prewritten functions that come with Excel.

    It is possible to create your own functions, by writing out in VBA (Visual Basic) what job you want done. these are User Defined functions.

    VBA is more flexible so you would not be limited to 7 if statements.

    You could find out more about them with a google search for Excel UDF. This is a page with more info and some examples.
    Regards
    John



  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Perhaps if you provided some details, someone could come up with a formula or lookup scenario that worked or help to create a UDF for you. Without some details, there are just too many possibilities...

    Steve

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    An alternative to nesting "IF" statements may be to use the "CHOOSE" function. Without knowing exactly what criteria you are evaluating, this may or may not apply to your case. If you want the value of a cell to be a certain value picked from a list (value1, value2, value3,...) based on the value of another cell or experssion (index_num), then this is the formula you want. CHOOSE(Index_num, value1, value2, value3,...)
    ex: CHOOSE(1, "hello", 6, E7, "good bye", 10/H5) Index_num of 1 selects the first value in the list "hello"
    CHOOSE(K2, "hello", 6, E7, "good bye", 10/H5) The value of cell K2 indicates the the selection of the value list. If cell K2= 3 then evaluates to the contents of cell E7
    CHOOSE(A1+B1, "hello", 6, E7, "good bye", 10/H5) The expression value of Cells A1 + B1 determines selection of the vaule list. If A1=3 and B1=2 then the formula
    evaluates to the 5th value in the list which is 10 divided by the vaule of cell H5
    This my shorten the process of elimination by nesting "If" statements if this is what you are trying to achieve. HTH

  10. #10
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Also, what version of Excel are you using?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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