Home 
Search 
Today's Posts 
#1




Why not nest more than 7 functions in Excel formula???
I'm trying to setup a worksheet to convert factors for product inventory...
I need to nest 50 or so functions...Any suggestions? 
#2




Yes.
Resort to Excel help and research LOOKUP, MATCH, and INDEX functions Use Google to search these newsgroups for simple approaches to similar problems Post a more detailed explanation of the type of calculation that you think requires you to nest 50 or so functions. "GrandCentral" wrote: I'm trying to setup a worksheet to convert factors for product inventory... I need to nest 50 or so functions...Any suggestions? 
#3




I'm ASSUMING that you are talking about the IF function.
FYI, The CHOOSE function can use up to 29 arguments. Otherwise, I think you'll need to create a macro function whose main programming would be the SELECT CASE statement. HTH, Gary Brown "GrandCentral" wrote: I'm trying to setup a worksheet to convert factors for product inventory... I need to nest 50 or so functions...Any suggestions? 
#4




I find it difficult to see why you need 50 nested functions. Can you give us
a description of what problem you are trying to solve (rather than a formula)?  Kind Regards, Niek Otten Microsoft MVP  Excel "GrandCentral" wrote in message ... I'm trying to setup a worksheet to convert factors for product inventory... I need to nest 50 or so functions...Any suggestions? 
#5




"GrandCentral" wrote...
I'm trying to setup a worksheet to convert factors for product inventory... I need to nest 50 or so functions...Any suggestions? And now for something completely different . . . If you also had OpenOffice installed, its spreadsheet, Calc, accomodates more than 7 nested function call levels, and such formulas *CAN* be saved in ..XLS files *AND* Excel will eveluate them correctly. TESTED & CONFIRMED! However, you can't edit such formulas in Excel. It's not a limitation of Excel's recalc facility or its file format. It's purely a limitation imposed by Excel's obviously ancient formula parser. That said, you could almost certainly use a lookup of some sort to do what you want. Also, even if Excel did provide more than 7 levels of nested function calls, 50 nested IFs would likely hit Excel's limit of 1024 on the character length of cell formulas. 
#6




Hi Harlan,
<OpenOffice installed, its spreadsheet, Calc, accomodates more than 7 nested function call levels, and such formulas *CAN* be saved in ..XLS files *AND* Excel will eveluate them correctly. TESTED & CONFIRMED! That is amazing! In previous versions of Excel we had some examples of MS not reusing code, like different results in the Evaluate Formula option, address intersections not working properly when using F9, different behavior of numberlike text in the function wizard, etc. I hope you don't mind I will check what you described he that would really be an example of bad programming practice! I can see that redeveloping a part of a formula evaluation sometimes is a bit quicker, but of course it is always the wrong direction to take in the end.  Kind Regards, Niek Otten Microsoft MVP  Excel "Harlan Grove" wrote in message ... "GrandCentral" wrote... I'm trying to setup a worksheet to convert factors for product inventory... I need to nest 50 or so functions...Any suggestions? And now for something completely different . . . If you also had OpenOffice installed, its spreadsheet, Calc, accomodates more than 7 nested function call levels, and such formulas *CAN* be saved in .XLS files *AND* Excel will eveluate them correctly. TESTED & CONFIRMED! However, you can't edit such formulas in Excel. It's not a limitation of Excel's recalc facility or its file format. It's purely a limitation imposed by Excel's obviously ancient formula parser. That said, you could almost certainly use a lookup of some sort to do what you want. Also, even if Excel did provide more than 7 levels of nested function calls, 50 nested IFs would likely hit Excel's limit of 1024 on the character length of cell formulas. 
#7




"Niek Otten" wrote...
.... I hope you don't mind I will check what you described he that would really be an example of bad programming practice! I have an example file located at ftp://members.aol.com/hrlngrv/nested.xls The formula is in cell C2. I can see that redeveloping a part of a formula evaluation sometimes is a bit quicker, but of course it is always the wrong direction to take in the end. ? If you mean a full rewrite seems to be in order, agreed. There's a lot of the original Mac Excel 1.0's functionality that has lingered a bit too long. 
#8




I have a conversion chart for Automatic Temperature Compensation.
factors vary, it depends on temperature. e.g.  if product temp = 0 c , then Factor is 1.0187 " = 1 c , then Factor is 1.0199 from 15 c to +30 c .... I want to eliminate the chart with a formula so type in the product temp and get the factor. (46 factors) and one chart with three different factor (720 factors) "Niek Otten" wrote: I find it difficult to see why you need 50 nested functions. Can you give us a description of what problem you are trying to solve (rather than a formula)?  Kind Regards, Niek Otten Microsoft MVP  Excel "GrandCentral" wrote in message ... I'm trying to setup a worksheet to convert factors for product inventory... I need to nest 50 or so functions...Any suggestions? 
#9




Look at the Vlookup() function. More info in HELP.
 Kind Regards, Niek Otten Microsoft MVP  Excel "GrandCentral" wrote in message ... I have a conversion chart for Automatic Temperature Compensation. factors vary, it depends on temperature. e.g.  if product temp = 0 c , then Factor is 1.0187 " = 1 c , then Factor is 1.0199 from 15 c to +30 c .... I want to eliminate the chart with a formula so type in the product temp and get the factor. (46 factors) and one chart with three different factor (720 factors) "Niek Otten" wrote: I find it difficult to see why you need 50 nested functions. Can you give us a description of what problem you are trying to solve (rather than a formula)?  Kind Regards, Niek Otten Microsoft MVP  Excel "GrandCentral" wrote in message ... I'm trying to setup a worksheet to convert factors for product inventory... I need to nest 50 or so functions...Any suggestions? 
#10




"GrandCentral" wrote in message ... I'm trying to setup a worksheet to convert factors for product inventory... I need to nest 50 or so functions...Any suggestions? With so many IF functions, I would suggest you create a User Defined Function /Fredrik 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Difference in number of Excel NewsGroups  Excel Discussion (Misc queries)  
ENTER EXCEL FORMULA WITH MORE THAN 7 NESTED FUNCTIONS  Excel Worksheet Functions  
Formating Excel functions  Excel Worksheet Functions  
Excel Lookup Functions  Excel Worksheet Functions  
Excel Lookup Functions  Excel Worksheet Functions 