Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 29th 05, 06:49 PM
GrandCentral
 
Posts: n/a
Default 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   Report Post  
Old March 29th 05, 06:57 PM
Duke Carey
 
Posts: n/a
Default

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

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

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

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

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.




  #7   Report Post  
Old March 29th 05, 11:32 PM
Harlan Grove
 
Posts: n/a
Default

"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   Report Post  
Old March 31st 05, 08:51 PM
GrandCentral
 
Posts: n/a
Default

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

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


"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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Difference in number of Excel NewsGroups Hari Prasadh Excel Discussion (Misc queries) 1 January 25th 05 11:32 AM
ENTER EXCEL FORMULA WITH MORE THAN 7 NESTED FUNCTIONS Linda Bolton Excel Worksheet Functions 2 January 14th 05 11:58 AM
Formating Excel functions MLRB Excel Worksheet Functions 3 November 18th 04 07:07 PM
Excel Lookup Functions Paul Adams Excel Worksheet Functions 0 November 10th 04 03:16 PM
Excel Lookup Functions Paul Adams Excel Worksheet Functions 1 November 10th 04 02:40 PM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017