[SOLVED] How many similar functions can be put in one cell?

[Biff]

Hi!

What's the formula look like?

What does "before it blows up" mean?

Biff

"Forrest" <[email protected]> wrote in message
news:[email protected]...
>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?

[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?

[Forrest]

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.

"Biff" wrote:

> Hi!
>
> What's the formula look like?
>
> What does "before it blows up" mean?
>
> Biff
>
> "Forrest" <[email protected]> wrote in message
> news:[email protected]...
> >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?
>
>
>

[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

"Forrest" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> "Biff" wrote:
>
>> Hi!
>>
>> What's the formula look like?
>>
>> What does "before it blows up" mean?
>>
>> Biff
>>
>> "Forrest" <[email protected]> wrote in message
>> news:[email protected]...
>> >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?
>>
>>
>>

[Ron Rosenfeld]

On Wed, 26 Oct 2005 11:16:06 -0700, "Forrest"
<[email protected]> wrote:

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

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

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

"Biff" wrote:

> 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
>
> "Forrest" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> > "Biff" wrote:
> >
> >> Hi!
> >>
> >> What's the formula look like?
> >>
> >> What does "before it blows up" mean?
> >>
> >> Biff
> >>
> >> "Forrest" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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?
> >>
> >>
> >>
>
>
>

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

"Ron Rosenfeld" wrote:

> On Wed, 26 Oct 2005 11:16:06 -0700, "Forrest"
> <[email protected]> wrote:
>
> >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?
>
> 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
>

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

"Ron Rosenfeld" wrote:

> On Wed, 26 Oct 2005 11:16:06 -0700, "Forrest"
> <[email protected]> wrote:
>
> >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?
>
> 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
>

[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


Forrest wrote:
> 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.
>
> "Biff" wrote:
>
>
>>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
>>
>>"Forrest" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>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.
>>>
>>>"Biff" wrote:
>>>
>>>
>>>>Hi!
>>>>
>>>>What's the formula look like?
>>>>
>>>>What does "before it blows up" mean?
>>>>
>>>>Biff
>>>>
>>>>"Forrest" <[email protected]> wrote in message
>>>>news:[email protected]...
>>>>
>>>>>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?
>>>>
>>>>
>>>>
>>
>>

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

[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


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.
>
> "Ron Rosenfeld" wrote:
>
>
>>On Wed, 26 Oct 2005 11:16:06 -0700, "Forrest"
>><[email protected]> wrote:
>>
>>
>>>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?
>>
>>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
>>

[Forrest]

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

"Roger Govier" wrote:

> 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
>
>
> Forrest wrote:
> > 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.
> >
> > "Biff" wrote:
> >
> >
> >>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
> >>
> >>"Forrest" <[email protected]> wrote in message
> >>news:[email protected]...
> >>
> >>>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.
> >>>
> >>>"Biff" wrote:
> >>>
> >>>
> >>>>Hi!
> >>>>
> >>>>What's the formula look like?
> >>>>
> >>>>What does "before it blows up" mean?
> >>>>
> >>>>Biff
> >>>>
> >>>>"Forrest" <[email protected]> wrote in message
> >>>>news:[email protected]...
> >>>>
> >>>>>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?
> >>>>
> >>>>
> >>>>
> >>
> >>
>

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

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.

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

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.

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

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,"# #/#\""")

[MrShorty]

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.

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,"# #/#\""")

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

[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 Grove wrote:
> 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.
>
>
> 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,"# #/#\""")
>

[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" wrote:

> 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.
>
> 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,"# #/#\""")
>
>

[Ron Rosenfeld]

On 27 Oct 2005 09:27:14 -0700, "Harlan Grove" <[email protected]> wrote:

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

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

[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?

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.

[Ron Rosenfeld]

On 27 Oct 2005 15:02:17 -0700, "Harlan Grove" <[email protected]> wrote:

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

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