Nesting Limit 2007

[bbal20]

I was under the impression that excel 2007 allowed more than 7 nested
finctions or up to 64, but I continue to get an error that says
"..........uses more level of functions than are allowed in the current
format." The formula is below.

=IF(MID($B2,39,3)="Eye","RM",IF(RIGHT($B2,2)="RM","RM",IF(ISNUMBER(SEARCH("Gum",$B2,1)),"RON",IF(ISNUMBER(SEARCH("EyeWonder",$B2,1)),"RM",IF(ISNUMBER(SEARCH("Video",$B2,1)),"RM",IF(ISNUMBER(SEARCH("Target",$B2,1)),"RON",IF(ISNUMBER(SEARCH("RM",$B2,1)),"RM","Not Valid")))))))

 

[Sheeloo]

Your workbook is probably still in XLS format... Excel 2007 imposes the
limits of 2003 in that format so that it can be kept compatible.

Save it as XLSX (2007 format) and it will allow you more than 7 IFs...

 

[bbal20]

How do I save in the XLSX format. It doesn't show as an option in the drop
down menu for the "save type as"?

 

[Sheeloo]

Choose Office Button->Save As->Excel Workbook
That will save in XLSX format (default for 2007)

Or Choose Office Button->Save As
then in Save as Type you will see Excel Workbook (.xlsx)

 

[Harlan Grove]

Your workbook is probably still in XLS format... Excel 2007 imposes the
limits of 2003 in that format so that it can be kept compatible.

....

Compatible with respect to Excel formulas needs refined defnition.

For example, you could use OpenOffice Calc to create a formula with 20
levels of nested function calls, save the worksheet containing that
formula in XLS format (OOo Calc will issue a warning about
incompatible features), then open that XLS file in Excel 2003. Excel
loads the file and calculates the formula without problems.

I've tested this with the following formula.

=SUM(SUM(SUM(SUM(SUM(SUM(SUM(SUM(SUM(SUM(SUM(SUM(SUM(SUM(
SUM(SUM(SUM(SUM(SUM(SUM(A21,A20),A19),A18),A17),A16),A15),
A14),A13),A12),A11),A10),A9),A8),A7),A6),A5),A4),A3),A2),A1)

You can change the values in any of the cells A1:A21, and Excel 2003
recalculates this formula without problems. The compatibility is in
Excel 2003's formula parser. While Excel will recalculate this
formula, it won't let you enter it or alter it, say, by changing the
reference to the corresponding cells in column D.

For this reason it seems odd Excel 2007 won't save such formulas in
XLS files and only issue a warning that users may not be able to EDIT
the XLS file in Excel 2003 and prior.

So let's not give Microsoft undue credit for greater limits in Excel
2007. For many previous versions, at least back to Excel 97, Excel was
able to recalculate formulas with more than 7 levels of nested
function calls in order to handle spreadsheets created by different
spreadsheet programs. Excel 2003 & prior could even save such files
after users made other changes. Microsoft just didn't bother to update
Excel's formula parser between 1985 and 2006 to allow Excel users to
enter or revise such formulas.

 

[Sheeloo]

Thanks Harlan,

I did not know...