Nested Ifs

AlexN · Apr 9th, 2002, 11:10 PM

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

Tom Schreiner · Apr 9th, 2002, 11:42 PM

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

Yogi Anand · Apr 9th, 2002, 11:58 PM

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.

Aladin Akyurek · Apr 10th, 2002, 01:36 AM

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 9th, 2002, 11:10 PM	#1
AlexN New Member Join Date: Apr 2002 Posts: 7	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

Apr 9th, 2002, 11:42 PM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	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

Apr 9th, 2002, 11:58 PM	#3
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	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.