Nested Ifs

AlexN

New Member
Joined
Apr 8, 2002
Messages
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top