IF (AND) functions

[Thomas]

I have written a very long IF(AND function. Up to 16
nested IF(AND functions. The logic still works and
provides a correct answer. But when I select the cell and
look at the function different characters appear in place
of commas (e.g. a chinese language symbol). Also the
function looks incomplete. If I attempt to click and edit
the cell, excel freezes.

 

[Jerry W. Lewis]

Excel only supports 7 nesting levels (see Help for "Excel specifications
and limits" Subtopic "Calculation specifications"). There is also a
limit on the length of formulas in a cell. I suspect that you have
somehow violated both limits in a way that Excel failed to catch, with
the result that unpredictable things happen.

What is the formula? It would be interesting to see if this is
reproducible.

Jerry

 

[Harlan Grove]

Excel only supports 7 nesting levels (see Help for "Excel specifications
and limits" Subtopic "Calculation specifications"). There is also a
limit on the length of formulas in a cell. I suspect that you have
somehow violated both limits in a way that Excel failed to catch, with
the result that unpredictable things happen.

What is the formula? It would be interesting to see if this is
reproducible. ...

FWIW, StarOffice/OpenOffice Calc and 123 all allow many more than 7 nested
levels of function calls, and all of them can save as .XLS files even when they
contain formulas with more than 7 nested levels of function calls. Indeed, I
creater the formulas

SO/OO Calc:
=SUM(A2;SUM(A3;SUM(A4;SUM(A5;SUM(A6;SUM(A7;SUM(A8;SUM(A9;SUM(A10;SUM(A11;
SUM(A12;SUM(A13;A14))))))))))))

123:
@SUM(A2,@SUM(A3,@SUM(A4,@SUM(A5,@SUM(A6,@SUM(A7,@SUM(A8,@SUM(A9,@SUM(A10,
@SUM(A11,@SUM(A12,@SUM(A13,A14))))))))))))

saved the workbooks containing them as oo.xls and 123.xls, and opened both in
XL97. The formulas come into Excel as

=SUM(A2,SUM(A3,SUM(A4,SUM(A5,SUM(A6,SUM(A7,SUM(A8,SUM(A9,SUM(A10,SUM(A11,
SUM(A12,SUM(A13,A14))))))))))))

(This was the result of [F2], [Shift]+[Home], [Ctrl]+[Insert], then [Esc].)

If I change any of the cells in A2:A14, the formula recalcs as expected. Well,
as it does in SO/OO Calc and 123. I don't suppose any behavior could be claimed
to be expected in Excel.

This means Excel's calculation mechanism has no problem dealing with functions
involving more than 7 nested levels of function calls. It's only the @#$%&*!
formula parser that can't cope. Cynics like me would jump to the conclusion that
Microsoft hasn't bothered to invest any resources on it since Excel 4 boldly
went where 123, Quattro Pro, Boeing Calc, Lucid 3D, etc. had already gone when
3D references were added for files in XL4 .XLW 'workbooks', and even that was
likely done with as little recoding of the original Excel 1.0 formula parser as
Microsoft could manage.

Anyway, this introduces some interesting possibilities - hybrid spreadsheet
development, using SO/OO Calc to build the formula portion of spreadsheet
models, then porting to Excel to add eyewash and other inessentials. The bad
news is that changing formulas would require using SO/OO Calc to do so, thus
possibly losing the eyewash.

Speaking as an Excel user who uses Word and PowerPoint as seldom as possible,
Microsoft hasn't earned its Office upgrade cost since Office 97. It's funny that
the richest software company in the world can't manage even relatively simple
new functionality like lifting the nested function call limit in the formula
parser since it's obvious that Excel's calculation mechanism can handle more
levels of nested function calls given the fact that XL97 can handle the formula
above. But recent history (XL2002 and XL2003) has shown that Microsoft is no
longer interested in adding spreadsheet-specific functionality to Excel.
Tinkering with existing functionality when shamed into doing so, sure, but not
adding functionality.

 

[Frank Kabel]

Harlan Grove wrote:
[....]

This means Excel's calculation mechanism has no problem dealing with
functions involving more than 7 nested levels of function calls. It's
only the @#$%&*! formula parser that can't cope.

Hi Harlan
now that's interesting! Looking at this one can only agree with you
that MS really should put some effort in adding REAL spreadsheet
functionality to an updated Excel. But as you I doubt that will happen
in the near future

Anyway, this introduces some interesting possibilities - hybrid
spreadsheet development, using SO/OO Calc to build the formula
portion of spreadsheet models, then porting to Excel to add eyewash
and other inessentials. The bad news is that changing formulas would
require using SO/OO Calc to do so, thus possibly losing the eyewash.

But nevertheless an interesting possibility


Frank

 

[Jerry W. Lewis]

FWIW, StarOffice/OpenOffice Calc and 123 all allow many more than 7 nested
levels of function calls, and all of them can save as .XLS files even when they
contain formulas with more than 7 nested levels of function calls. ....

This means Excel's calculation mechanism has no problem dealing with functions
involving more than 7 nested levels of function calls.

Interesting data point. Thanks for providing it!

Jerry

 

[Guest]

Thank you all for your suggestions. They helped. The
problem was not the number of nested functions but the
number of characters! I shortened the page references
with abberviations to reduce the number of characters in
the logical equation.

-----Original Message-----
Harlan Grove wrote:
[....]

This means Excel's calculation mechanism has no problem dealing with
functions involving more than 7 nested levels of function calls. It's
only the @#$%&*! formula parser that can't cope.

Hi Harlan
now that's interesting! Looking at this one can only agree with you
that MS really should put some effort in adding REAL spreadsheet
functionality to an updated Excel. But as you I doubt that will happen
in the near future

Anyway, this introduces some interesting possibilities - hybrid
spreadsheet development, using SO/OO Calc to build the formula
portion of spreadsheet models, then porting to Excel to add eyewash
and other inessentials. The bad news is that changing formulas would
require using SO/OO Calc to do so, thus possibly losing

the eyewash.

But nevertheless an interesting possibility


Frank

.