ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Why not nest more than 7 functions in Excel formula??? (https://www.excelbanter.com/excel-worksheet-functions/19663-why-not-nest-more-than-7-functions-excel-formula.html)

GrandCentral March 29th 05 06:49 PM

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?

Duke Carey March 29th 05 06:57 PM

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?


Gary Brown March 29th 05 07:03 PM

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?


Niek Otten March 29th 05 08:53 PM

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?




Harlan Grove March 29th 05 09:05 PM

"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 March 29th 05 09:39 PM

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.





Harlan Grove March 29th 05 11:32 PM

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



GrandCentral March 31st 05 08:51 PM

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?





Niek Otten March 31st 05 09:04 PM

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?







Fredrik Wahlgren March 31st 05 09:30 PM


"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




All times are GMT +1. The time now is 12:04 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
ExcelBanter.com