asked on

The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format

I am not too familiar with excel and am trying to create a spreadsheet using existing formulas. However I get the message "The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format". I do not get this error when using excel 2010 however if I try to save it as a .xls file I get the above error. For the sample formula I have included how would you rewrite this so I do not receive the error.

=IF(F79=1,'Take Off'!E120,IF(F79=2,'Take Off'!E121,IF(F79=3,'Take Off'!E122,IF(F79=4,'Take Off'!E123,IF(F79=5,'Take Off'!E124,IF(F79=6,'Take Off'!E125,IF(F79=7,'Take Off'!E126,IF(F79=8,'Take Off'!E127,IF(F79=9,'Take Off'!E128,0)))))))))

Open in new window

cyberkiwi

Each version of Excel has a maximum level of nesting of formulas, 2010 obviously more than 2003 (.xls format).
You need to think of another way to write the formula.
I believe in 2003, it is 10 levels of nesting.

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cyberkiwi

FYI - it's actually 7 levels for 2003, 64 from 2007 up.

byundt

If you want a value of 0 rather than FALSE if F79 is not 1 through 9, then consider:
=IF(ISNA(MATCH(F79,ROW($1:$9),0)),0,INDEX('Take Off'!E120:E128,F79))