Allow more than 7 nested IF THEN tests in EXCEL. Allow 30 or more

[R]

Allow more than 7 nested IF THEN tests in EXCEL. Allow 30 or more

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions

 

[tjtjjtjt]

It's coming soon:

Excel 12:
The number of levels of nesting that Excel allows in formulas
Old Limit: 7
New Limit: 64
http://blogs.msdn.com/excel/archive/category/11360.aspx

 

[Harlan Grove]

tjtjjtjt wrote...

It's coming soon:

....

In a way, it's been available for over a decade - just not in Excel.
123R5 handles 22 nested levels and 49 arguments. OpenOffice 2.0 Calc
handles 39 nested levels and has no limit on the number of arguments
other than its limit on formula length. Gnumeric 1.6.0 handles at least
100 nested levels (I got bored writing the formula, so that's where I
stopped) and like OO Calc is only limited in the number of arguments by
the limit on formula length.

The number of nested levels may be limited by a static size call stack
(which Microsoft is boldly increasing for the first time since Excel
version 1.0). The limit on the number of arguments is purely
artificial. Excel could store the results of evaluated arguments in the
dynamically allocated heap, like it almost certainly stores evaluated
arrays of millions of elements, e.g.,
{=SUM(0,(ROW(1:65535)-1)*64+COLUMN(C:BN)-2,0)}, which evaluates
correctly to 8,795,826,685,920. It's very hard to come up with a reason
Excel 12 will still be limited to 255 arguments when Excel 10 (aka 2002
or XP, which I'm using right now) can handle individual arguments
returning arrays of millions of numbers.

 

[Biff]

One could always use the concatenation technique which isn't nesting. The
limit being the formula length itself.

Biff