How many similar functions can be put in one cell?

F

Forrest

I have a formula in one cell with several functions used multiple times. It
seems though that 25 times is the max for the Round function before it blows
up. Why?
 
B

Biff

So, you're trying to convert a value into feet and fractions of an inch.

What is in A28 and what does 3.2808 have to do with it?

That is one nasty formula, 928 characters! <g>

Why don't you explain exactly what you're trying to do and maybe we can come
up something more manageable.

Biff
 
R

Ron Rosenfeld

I have a formula in one cell with several functions used multiple times. It
seems though that 25 times is the max for the Round function before it blows
up. Why?
Click to expand...

The error has nothing to do with the ROUND function. You have run into the
seven level function nesting limitation of Excel.

What are you trying to do? Perhaps someone can suggest an shorter formula.


--ron
 
F

Forrest

Congratulations on figuring out what the equation does. 3.2808 is the
conversion from meters to feet. A28 is simply the cell with the metric
length to convert.
The purpose is to copy this one cell formula into any spreadsheet next to a
cell with a metric length to convert. If I spilt up the equation into two
cells it works fine, but that defeats my goal.
 
F

Forrest

Can you point out at where the nesting is being exceeded? I have only seven
nested if statements. The equation converts meters into feet, inches and
fractions of an inch to the nearest eighth. It works fine if I spilt it into
2 cells, but I don't want that.
 
F

Forrest

I think I found it. Since I have two orders nested within the If functions
it can't operate past the 5th If. Why doesn't Microsoft come into the 21st
century and eliminate this silly nesting restriction.
 
R

Roger Govier

Hi Forrest

Try
=INT(CONVERT(E17,"m","ft"))&" feet "&INT(MOD(CONVERT(E17,"m","ft"),1)*12)&
" "&CHOOSE((ROUND(((E17*3.2808-INT(ROUND(E17*3.2808,2)))
*12-TRUNC((E17*3.2808-INT(ROUND(E17*3.2808,2)))*12))/0.125,0))
,"1/8","1/4","3/8","1/2","5/8","3/4","7/8")&" inches"

I used the convert function, to go from metres to feet (I think you need the
Analysis Toolpak loaded for this Tolls>Addins>Analysis Toolpak).
I couldn't be bothered to alter your calculation for the fractions of an
inch, I just made it one calculation with a choose dependent upon its result.

With 10 in E16 it returns 32 feet 9 3/4 inches.


Regards

Roger Govier
 
R

Roger Govier

Hi Forrest

You can replace a whole heap of your IF statement with one CHOOSE.
See my earlier posting, and watch out for line wraps as you copy and paste.

Regards

Roger Govier
 
M

MrShorty

Here's one I came up with:

INT(RC[-1]*3.2808)&"ft
"&TEXT(MOD(INT(ROUND(RC[-1]*3.2808*12*8,0))/8,12),"# #/#")&"in"

Obviously the initial INT function returns the number of feet. The
inner INT(ROUND()) function rounds to the nearest 1/8 inch, the MOD
function returns the remaing inches after lopping of the number of
feet, and the TEXT function formats the output to show the number of
inches correctly.
 
H

Harlan Grove

Forrest wrote...
Can you point out at where the nesting is being exceeded? I have only seven
nested if statements. The equation converts meters into feet, inches and
fractions of an inch to the nearest eighth. It works fine if I spilt it into
2 cells, but I don't want that.
Click to expand...
....

It's not a limitation on the number of nested IF calls, it's a
limitation on the number of nested *function* calls. Your formula tries
to go 10 levels deep.
 
H

Harlan Grove

Forrest wrote...
I think I found it. Since I have two orders nested within the If functions
it can't operate past the 5th If. Why doesn't Microsoft come into the 21st
century and eliminate this silly nesting restriction.
Click to expand...
....

The good news is that Microsoft will finally raise this limit (one
which has set Excel appart from other spreadsheets for decades - at the
LOW end of the capability range in this regard) in the next version.
 
F

Forrest

Thank you very much. I've never used the "choose" function before. I'll
definately try it.
 
H

Harlan Grove

MrShorty wrote...
Here's one I came up with:

INT(RC[-1]*3.2808)&"ft
"&TEXT(MOD(INT(ROUND(RC[-1]*3.2808*12*8,0))/8,12),"# #/#")&"in"

Obviously the initial INT function returns the number of feet. The
inner INT(ROUND()) function rounds to the nearest 1/8 inch, the MOD
function returns the remaing inches after lopping of the number of
feet, and the TEXT function formats the output to show the number of
inches correctly.
Click to expand...

Your feet term will be trouble when INT's argument evaluates to less
that 1/16" shy of a whole foot.

I came up with

=INT(ROUND(A28*3.2808*96,0)/96)&"' - "
&TEXT(MOD(ROUND(A28*3.2808*96,0),96)/8,"# #/#\""")
 
R

Roger Govier

Very nice Harlan.

And it returns the correct value when you get down to .304 metres, which
mine and Mr Shorty's get wrong.

Regards

Roger Govier


Harlan said:
MrShorty wrote...
Here's one I came up with:

INT(RC[-1]*3.2808)&"ft
"&TEXT(MOD(INT(ROUND(RC[-1]*3.2808*12*8,0))/8,12),"# #/#")&"in"

Obviously the initial INT function returns the number of feet. The
inner INT(ROUND()) function rounds to the nearest 1/8 inch, the MOD
function returns the remaing inches after lopping of the number of
feet, and the TEXT function formats the output to show the number of
inches correctly.
Click to expand...


Your feet term will be trouble when INT's argument evaluates to less
that 1/16" shy of a whole foot.

I came up with

=INT(ROUND(A28*3.2808*96,0)/96)&"' - "
&TEXT(MOD(ROUND(A28*3.2808*96,0),96)/8,"# #/#\""")
Click to expand...
 
M

MrShorty

Harlan said:
MrShorty wrote...
Here's one I came up with:

INT(RC[-1]*3.2808)&"ft
"&TEXT(MOD(INT(ROUND(RC[-1]*3.2808*12*8,0))/8,12),"# #/#")&"in"

Obviously the initial INT function returns the number of feet. The
inner INT(ROUND()) function rounds to the nearest 1/8 inch, the MOD
function returns the remaing inches after lopping of the number of
feet, and the TEXT function formats the output to show the number of
inches correctly.
Click to expand...

Your feet term will be trouble when INT's argument evaluates to less
that 1/16" shy of a whole foot.

I came up with

=INT(ROUND(A28*3.2808*96,0)/96)&"' - "
&TEXT(MOD(ROUND(A28*3.2808*96,0),96)/8,"# #/#\""")
Click to expand...

Good catch. So you simply replaced my erroneous argument for the INT
function with the same argument as used in the MOD function (so INT and
MOD are working with the same value).
 
F

Forrest

Wow, I am truly impressed. Here's what I finally came up with; it works
well, but yours is much shorter.

=(INT(ROUND(B5*3.2808,2)))&"' -
"&(TRUNC(ROUND(((B5*3.2808-(INT(ROUND(B5*3.2808,2))))*12),1)))&"
"&(IF((ROUND((((B5*3.2808-(INT(ROUND(B5*3.2808,2))))*12)-(TRUNC(ROUND(((B5*3.2808-(INT(ROUND(B5*3.2808,2))))*12),1))))/0.125,0))>0,CHOOSE((ROUND((((B5*3.2808-(INT(ROUND(B5*3.2808,2))))*12)-(TRUNC(ROUND(((B5*3.2808-(INT(ROUND(B5*3.2808,2))))*12),1))))/0.125,0)),"1/8","1/4","3/8","1/2","5/8","3/4","7/8",""),""))&""""

Thanks for your help


Harlan Grove said:
MrShorty wrote...
Here's one I came up with:

INT(RC[-1]*3.2808)&"ft
"&TEXT(MOD(INT(ROUND(RC[-1]*3.2808*12*8,0))/8,12),"# #/#")&"in"

Obviously the initial INT function returns the number of feet. The
inner INT(ROUND()) function rounds to the nearest 1/8 inch, the MOD
function returns the remaing inches after lopping of the number of
feet, and the TEXT function formats the output to show the number of
inches correctly.
Click to expand...

Your feet term will be trouble when INT's argument evaluates to less
that 1/16" shy of a whole foot.

I came up with

=INT(ROUND(A28*3.2808*96,0)/96)&"' - "
&TEXT(MOD(ROUND(A28*3.2808*96,0),96)/8,"# #/#\""")
Click to expand...
 
R

Ron Rosenfeld

Forrest wrote...
...

The good news is that Microsoft will finally raise this limit (one
which has set Excel appart from other spreadsheets for decades - at the
LOW end of the capability range in this regard) in the next version.
Click to expand...

Harlan,

Didn't you also point out that the problem is the parser, and that equations
with more than seven nested functions that were created in other programs would
work OK in Excel?


--ron
 
H

Harlan Grove

Ron Rosenfeld wrote...
....
Didn't you also point out that the problem is the parser, and that equations
with more than seven nested functions that were created in other programs would
work OK in Excel?
Click to expand...

Whether Excel's formula parser is the problem is speculation. However,
OpenOffice accepts the OP's original formula (once commas are changed
to semicolons), and gives the intended result. If the file were saved
in XLS format, Excel has no problem opening the file and
using/recalculating the formula as-is. Excel just can't edit it.

So, yes, the OP could install OpenOffice and use it to create deeply
nested formulas, save as XLS, then use (but not edit) in Excel.
 
R

Ron Rosenfeld

Ron Rosenfeld wrote...
...

Whether Excel's formula parser is the problem is speculation. However,
OpenOffice accepts the OP's original formula (once commas are changed
to semicolons), and gives the intended result. If the file were saved
in XLS format, Excel has no problem opening the file and
using/recalculating the formula as-is. Excel just can't edit it.

So, yes, the OP could install OpenOffice and use it to create deeply
nested formulas, save as XLS, then use (but not edit) in Excel.
Click to expand...

Thanks for refreshing my memory on that point.
--ron
 
Top