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?

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?

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?

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?

"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.

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 he 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" 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.

"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 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.

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?

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?

"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