Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2991
  • Last Modified:

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

0
Shumai1
Asked:
Shumai1
  • 3
1 Solution
 
cyberkiwiCommented:
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.
0
 
cyberkiwiCommented:
=IF(AND(F79>=1,F79<=9),INDEX('Take Off'!E120:E128, F79))
0
 
cyberkiwiCommented:
FYI - it's actually 7 levels for 2003, 64 from 2007 up.
0
 
byundtCommented:
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))
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now