Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting Limit 2007
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(ISN UMBER(SEARCH("EyeWonder",$B2,1)),"RM",IF(ISNUMBER( SEARCH("Video",$B2,1)),"RM",IF(ISNUMBER(SEARCH("Ta rget",$B2,1)),"RON",IF(ISNUMBER(SEARCH("RM",$B2,1) ),"RM","Not Valid"))))))) -- Thank you for your help and support |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting Limit 2007
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" wrote: 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(ISN UMBER(SEARCH("EyeWonder",$B2,1)),"RM",IF(ISNUMBER( SEARCH("Video",$B2,1)),"RM",IF(ISNUMBER(SEARCH("Ta rget",$B2,1)),"RON",IF(ISNUMBER(SEARCH("RM",$B2,1) ),"RM","Not Valid"))))))) -- Thank you for your help and support |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting Limit 2007
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"? -- Thank you for your help and support "Sheeloo" wrote: 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" wrote: 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(ISN UMBER(SEARCH("EyeWonder",$B2,1)),"RM",IF(ISNUMBER( SEARCH("Video",$B2,1)),"RM",IF(ISNUMBER(SEARCH("Ta rget",$B2,1)),"RON",IF(ISNUMBER(SEARCH("RM",$B2,1) ),"RM","Not Valid"))))))) -- Thank you for your help and support |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting Limit 2007
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) "bbal20" wrote: 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"? -- Thank you for your help and support "Sheeloo" wrote: 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" wrote: 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(ISN UMBER(SEARCH("EyeWonder",$B2,1)),"RM",IF(ISNUMBER( SEARCH("Video",$B2,1)),"RM",IF(ISNUMBER(SEARCH("Ta rget",$B2,1)),"RON",IF(ISNUMBER(SEARCH("RM",$B2,1) ),"RM","Not Valid"))))))) -- Thank you for your help and support |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting Limit 2007
Sheeloo wrote...
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(S UM(SUM( SUM(SUM(SUM(SUM(SUM(SUM(A21,A20),A19),A18),A17),A1 6),A15), A14),A13),A12),A11),A10),A9),A8),A7),A6),A5),A4),A 3),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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting Limit 2007
Thanks Harlan,
I did not know... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 problem nesting IF & AND | Excel Worksheet Functions | |||
IF condition Nesting limit error | New Users to Excel | |||
IF condition Nesting limit error | New Users to Excel | |||
IF condition Nesting limit error | New Users to Excel | |||
Have I exceeded nesting limit | Excel Worksheet Functions |