Thanks:  0
Likes:  0

1. 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. 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. 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. 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)

## User Tag List

#### Posting Permissions

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