why only 7 levels of if?

[Sybmathics]

Hi all,

This week I met with a problem where I couldn't solve the problem of
several layers of if-functions with the usual vlookup.

I realy needed 6 levels of nested if-functions, which looked at already
2 nested if-functions. When I tried to put it all together in just 1
function statement I received the error message that i overruled the
current possible levels.

Why are there only 7 levels of if-s, where you could easily put 3
functions in each if logical test (leading up to much more than 7 nested
functions)? The other day I wrote a formula with 21 nested funtions and
it worked perfectly (one of the reasons why i'm a great fan of excel).

So where does the limitation with IF come from?
Also, is there a difference with Office versions in the number of
possible levels of nesting with IF?

I'm sure that one of your technical people can help me out here.

Any help is greatly appreciated.

Greets

Sybolt

 

[Peo Sjoblom]

In Excel 2007 you can have has 64 nested IFs

regardless you can always overcome the limitations by using other solutions.


--


Regards,


Peo Sjoblom

 

[Bob I]

FWIW in 2007 the number is 64

 

[T. Valko]

The other day I wrote a formula with 21 nested funtions and it worked

perfectly

Don't confuse the *number of functions* a formula contains with the *number
of nested levels*.

The limit of nested levels pertains to *all* functions, not just the IF
function.

 

[Sybmathics]

T. Valko rote:

The limit of nested levels pertains to *all* functions, not just the IF
function.

Ok, I understand that,

My question comes down to: why the magical border of 7.

Where does it come from.

 

[T. Valko]

My question comes down to: why the magical border of 7.

Where does it come from.

That's just the way Microsoft wrote the application. They must have figured
that no one would ever need more! But, as others have noted, this limit is
increased in Excel 2007.

 

[Harlan Grove]

My question comes down to: why the magical border of 7.

Where does it come from.

It comes from how Excel's formula PARSER was written. It has NOTHING
to do with Excel's recalculation functionality.

The formula parser is the bit that takes what you enter for cell
contents either in the formula bar or directly in the cell and
converts it into the binary representation Excel stores in memory and
in XLS disk files. The restriction may have made sense back in Mac
512K days when RAM was a very precious resource. Handling arbitrary
nesting levels would have required large RAM allocation for deep parse
trees. Since formula length way back then maxed out at 255 chars, 7
nested levels was reasonable. What wasn't reasonable was increasing
formula length without increasing nesting levels.

As for proof that it has nothing to do with recalculation, create a
spreadsheet using a different spreadsheet program, e.g., OpenOffice
Calc. I created one with the formula

=IF(A1;1;IF(A2;2;IF(A3;3;IF(A4;4;IF(A5;5;IF(A6;6;IF(A7;7;
IF(A8;8;IF(A9;9;IF(A10;10;IF(A11;11;IF(A12;12;"foo"))))))))))))

in cell A14 and nothing in any other cell, saved it as an XLS file,
opened it in Excel 2003, and Excel loads it with no complaints and
evaluates A14 as "foo". Then I enter 1 in A12, and A14 evaluates as
12.

So how did Microsoft get away with such a limited formula parser for
so long? SPECULATION: It wasn't a problem through Excel 5 because the
formula length limit made it difficult to reach 7 nested levels in
real world rather than contrived formulas. From Excel 97 on Microsoft
has been effectively free of pesky competitors, so they could and did
do as little as possible in each upgrade. They had to increase formula
length to accommodate long filenames, but they didn't bother updating
the formula parser beyond that.

 

[Sybmathics]

OK.

Harlan, thanks very much.

I always wondered what was happening.

greets,

Sybolt