change decimal to feet and inches

[Eyde]

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?

 

[Ron Rosenfeld]

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

 

[Gary''s Student]

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

 

[James Silverton]

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

 

[James Silverton]

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

 

[Bernard Liengme]

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

 

[Ron Rosenfeld]

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

 

[James Silverton]

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

 

[Rod Sow]

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

 

[Bob Phillips]

What formula?

--
HTH

Bob

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

 

[Niek Otten]

What formulas?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

in message news:[email protected]...