Why not nest more than 7 functions in Excel formula???

[Guest]

I'm trying to setup a worksheet to convert factors for product inventory...
I need to nest 50 or so functions...Any suggestions?

 

[Guest]

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.

 

[Guest]

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

 

[Niek Otten]

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

 

[Harlan Grove]

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.

 

[Niek Otten]

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 re-using code,
like different results in the Evaluate Formula option, address intersections
not working properly when using F9, different behavior of number-like text
in the function wizard, etc.
I hope you don't mind I will check what you described here: that would
really be an example of bad programming practice!
I can see that re-developing 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]

I hope you don't mind I will check what you described here: 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 re-developing 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.

 

[Guest]

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]

Look at the Vlookup() function. More info in HELP.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Fredrik Wahlgren]

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