How many similar functions can be put in one cell?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
Hi!

What's the formula look like?

What does "before it blows up" mean?

Biff
 
It looks like this:

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

What occurs is Excel says there is an error in the formula, once it gets to
a certain size and highlights one of the functions. In this case it is the
Round function. There isn't a logic or syntax error though. It could be a
nesting problem. I have seven nested If statements, each with 3 Round
functions.
This equation has 59 functions, so I am guessing that there is a limit to
the number in one cell.
 
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
 
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
 
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.
 
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.
 
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.
 
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
 
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
 
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.
 
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.
 
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.
 
Thank you very much. I've never used the "choose" function before. I'll
definately try it.
 
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,"# #/#\""")
 
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...
 
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).
 
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...
 
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
 
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.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Editing an "IF" function across a worksheet/excel file 2
Why does this confuse me?? -- ? on Offset function 1
IF function says too many nested 1
want to track time while paste value in multiple column of same roe 1
UDF Function 1
IF AND Nested Functions - Formula Not Working 0
Excel Trying to code an Array Function in VBA Excel to works as a MaxIF 3
Round Function 5

Back
Top