Remember Me?

#1
March 29th 05, 06:49 PM
 GrandCentral Posts: n/a
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
March 29th 05, 06:57 PM
 Duke Carey Posts: n/a

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
March 29th 05, 07:03 PM
 Gary Brown Posts: n/a

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
March 29th 05, 08:53 PM
 Niek Otten Posts: n/a

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
March 29th 05, 09:05 PM
 Harlan Grove Posts: n/a

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

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
March 29th 05, 09:39 PM
 Niek Otten Posts: n/a

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

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
March 29th 05, 11:32 PM
 Harlan Grove Posts: n/a

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

#8
March 31st 05, 08:51 PM
 GrandCentral Posts: n/a

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
March 31st 05, 09:04 PM
 Niek Otten Posts: n/a

--

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
March 31st 05, 09:30 PM
 Fredrik Wahlgren Posts: n/a

"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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Hari Prasadh Excel Discussion (Misc queries) 1 January 25th 05 11:32 AM Linda Bolton Excel Worksheet Functions 2 January 14th 05 11:58 AM MLRB Excel Worksheet Functions 3 November 18th 04 07:07 PM Paul Adams Excel Worksheet Functions 0 November 10th 04 03:16 PM Paul Adams Excel Worksheet Functions 1 November 10th 04 02:40 PM

All times are GMT +1. The time now is 12:45 PM.