I need to know how to convert decimal numbers that could at eny given time be
zero decimal places up to three decimal places (ie 5, 3.5, 5.33, 12.713) to
feet and inches (ie 5'-0", 3'-6", 5'-4", 12'-8 1/2") this excel formula is
going to be linked with an AutoCad table can any one help me with this?

On Fri, 22 Feb 2008 10:21:02 -0800, Eyde
wrote:

I need to know how to convert decimal numbers that could at eny given time be
zero decimal places up to three decimal places (ie 5, 3.5, 5.33, 12.713) to
feet and inches (ie 5'-0", 3'-6", 5'-4", 12'-8 1/2") this excel formula is
going to be linked with an AutoCad table can any one help me with this?

What kind of input does the AutoCad table require -- if it is in the same
format as you show, then:

=INT(A1)&"'--"&IF(INT(ROUND(MOD(A1,1)*12*4,0)/4)=
ROUND(MOD(A1,1)*12*4,0)/4,TEXT(ROUND(MOD(A1,1)*
12*4,0)/4,"#0\"""),TEXT(ROUND(MOD(A1,1)*12*4,0)/4,"# ?/?\"""))

will give you a result in the same format with the fractional inches rounded to
the nearest 1/4.

If you need a different rounding parameter, for instance, to the nearest 1/8 or
1/16, then change the all the 4's in the above to 8 or 16.

If you change to 16ths or smaller, be sure to change the ?/? to ?/?? also

--ron

Let's say that cell A1 contains a length in feet: 12.1 then in cell B1 put:

=INT(A1)&"' "&INT(12*(A1-INT(A1)))&""" "&TEXT(MOD(12*(A1-INT(A1)),1),"##/##")

which will display as 12" 1" 1/5

If you need the reverse, put the feet and inches into separate cells to make
the math easier

--
Gary''s Student - gsnu200770


"Eyde" wrote:

I need to know how to convert decimal numbers that could at eny given time be
zero decimal places up to three decimal places (ie 5, 3.5, 5.33, 12.713) to
feet and inches (ie 5'-0", 3'-6", 5'-4", 12'-8 1/2") this excel formula is
going to be linked with an AutoCad table can any one help me with this?

Gary''s wrote on Fri, 22 Feb 2008 11:22:01 -0800:

GsS =INT(A1)&"' "&INT(12*(A1-INT(A1)))&"""
GsS "&TEXT(MOD(12*(A1-INT(A1)),1),"##/##")

GsS which will display as 12" 1" 1/5

GsS If you need the reverse, put the feet and inches into
GsS separate cells to make the math easier

GsS --
GsS Gary''s Student - gsnu200770

GsS "Eyde" wrote:

?? I need to know how to convert decimal numbers that could
?? at eny given time be zero decimal places up to three
?? decimal places (ie 5, 3.5, 5.33, 12.713) to feet and
?? inches (ie 5'-0", 3'-6", 5'-4", 12'-8 1/2") this excel
?? formula is going to be linked with an AutoCad table can
?? any one help me with this?

I managed something your formula when I tried tho' I was content
with an integral number of inches, even if I had to relearn a
number of functions (as usual), but then I wondered if it would
be possible to devise a custom format and I'm currently stuck
:-)


James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not

Gary''s wrote on Fri, 22 Feb 2008 11:22:01 -0800:

GsS =INT(A1)&"' "&INT(12*(A1-INT(A1)))&"""
GsS "&TEXT(MOD(12*(A1-INT(A1)),1),"##/##")

Having once had the capability available to use recursion and
temporary variables in a formula (MLAB by Gary Knott), I still
miss it in Excel. I guess it is possible in a macro. It always
seems inelegant to have to calculate something more than once,
like (A1-INT(A1) ). It probably doesn't really make all that
much difference in speed.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not

Would formatting with # ??/12 work for you?
Then you still have numeric values
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Eyde" wrote in message
...
I need to know how to convert decimal numbers that could at eny given time
be
zero decimal places up to three decimal places (ie 5, 3.5, 5.33, 12.713)
to
feet and inches (ie 5'-0", 3'-6", 5'-4", 12'-8 1/2") this excel formula is
going to be linked with an AutoCad table can any one help me with this?

On Fri, 22 Feb 2008 15:01:25 -0500, "James Silverton"
wrote:

Gary''s wrote on Fri, 22 Feb 2008 11:22:01 -0800:

GsS =INT(A1)&"' "&INT(12*(A1-INT(A1)))&"""
GsS "&TEXT(MOD(12*(A1-INT(A1)),1),"##/##")

Having once had the capability available to use recursion and
temporary variables in a formula (MLAB by Gary Knott), I still
miss it in Excel. I guess it is possible in a macro. It always
seems inelegant to have to calculate something more than once,
like (A1-INT(A1) ). It probably doesn't really make all that
much difference in speed.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not

Well, here's a VBA version:

================================
Option Explicit
Function FtIn(n As Double, Optional Rnd) As Variant
'Rnd is an optional fractional denominator rounding
'in the range of 1-99
Dim dFt As Double
Dim dIn As Double
Dim Fmt As String

dFt = Int(n)
dIn = (n - dFt) * 12
If Not IsMissing(Rnd) Then
If Rnd < 1 Or Rnd 99 Then
FtIn = CVErr(xlErrNum)
Exit Function
End If
dIn = Round(dIn * Rnd, 0) / Rnd
End If
Fmt = IIf(dIn = Int(dIn), "#0\'", "#0 ?/??\'")
FtIn = dFt & "'--" & WorksheetFunction.Text(dIn, Fmt)
End Function
==============================
--ron

Ron wrote on Fri, 22 Feb 2008 16:00:25 -0500:

?? Gary''s wrote on Fri, 22 Feb 2008 11:22:01 -0800:
??
GsS =INT(A1)&"' "&INT(12*(A1-INT(A1)))&"""
GsS "&TEXT(MOD(12*(A1-INT(A1)),1),"##/##")
??
?? Having once had the capability available to use recursion
?? and temporary variables in a formula (MLAB by Gary Knott),
?? I still miss it in Excel. I guess it is possible in a
?? macro. It always seems inelegant to have to calculate
?? something more than once, like (A1-INT(A1) ). It probably
?? doesn't really make all that much difference in speed.
RR Well, here's a VBA version:

RR ================================
RR Option Explicit
RR Function FtIn(n As Double, Optional Rnd) As Variant
RR 'Rnd is an optional fractional denominator rounding
RR 'in the range of 1-99
RR Dim dFt As Double
RR Dim dIn As Double
RR Dim Fmt As String

Thanks, I had not gotten around to trying for myself since it
was just an interesting problem to me. I won't quote all the
VBA.


James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not

The formulas above work, but not for negative numbers. For example: -3.42' shows as -4'--7". Does anyone know how to correct this?

What formula?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

<Rod Sow wrote in message ...
The formulas above work, but not for negative numbers. For
xample: -3.42' shows as -4'--7". Does anyone know how to correct this?

What formulas?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Rod Sow" wrote in message ...
The formulas above work, but not for negative numbers. For
xample: -3.42' shows as -4'--7". Does anyone know how to correct this?