Nested Ifs
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Nested Ifs

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Is there a maximum number of nested if statements?

    I have ended up with a long string of nested ifs and it is giving me an error in one, however as I can't see the error I am wondering if there is a maximum length?

    As an alternative.

    I have 4 tables of comparitive values in seperate sheets. Say Cell B9 in sheets 1-4.

    Is there a command where I can get excel to give me the largest value, or a test string indicating the largest value?

    Thanks

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    I'm not sure if it has changed in 2000, but
    I believe 7 nested is the limit...
    There is a worksheet function called Max.
    =MAX(B2:B8)
    would return the greatest number in the chosen range.
    Tom

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom is right -- there is a limit of 7 nested functions in any formula; for maximum value you can use the MAX function as Tom said, or you can also LARGE function to get the Largest value and then the 2nd largest, and the 3rd largest, and so on.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,872
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    On 2002-04-09 22:10, AlexN wrote:
    Is there a maximum number of nested if statements?

    I have ended up with a long string of nested ifs and it is giving me an error in one, however as I can't see the error I am wondering if there is a maximum length?

    As an alternative.

    I have 4 tables of comparitive values in seperate sheets. Say Cell B9 in sheets 1-4.

    Is there a command where I can get excel to give me the largest value, or a test string indicating the largest value?

    Thanks
    Alex,

    What follows would illustrate the nesting limits:

    While both

    =IF(8,8,IF(7,7,IF(6,6,IF(5,5,IF(4,4,IF(3,3,IF(2,2,IF(1,1,1))))))))

    and

    =MAX(MAX(MAX(MAX(MAX(MAX(MAX(MAX(1,2),3),4),8),7),0),5),4)

    will be evaluated,

    =IF(9,9,IF(8,8,IF(7,7,IF(6,6,IF(5,5,IF(4,4,IF(3,3,IF(2,2,IF(1,1,1)))))))))

    and

    =MAX(MAX(MAX(MAX(MAX(MAX(MAX(MAX(MAX(0,1),2),3),4),5),6),7),8),9)

    will not. What I'm trying to say is that the limit rule is:

    The outermost function in a formula is not allowed to wrap more than 7 nested functions.

    Regarding IF, it is already hard to devise 4 nested IFs and debug them whenever a trouble arises. For that reason, such constructions should better be avoided. Fortunately, the need/requirement for nested IFs can be often restated by using a lookup function like VLOOKUP.

    If I understood your MAX question properly, you appear to want a 3-D formula:

    =MAX(Sheet1:Sheet4!B9)

    Aladin



User Tag List

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
  •  

 

 
DMCA.com