formula to convert decimal feet to feet inch sixteenths

[tv]

I would like to write a formula in excel, without using macros, to convert
decimal feet to feet-inch-sixteenths.

Can someone help me ?

 

[Bob Phillips]

Is this any good?

=TEXT(INT(D18)," 0 ""feet """)&INT(MOD(D18,1)*12)&"
"&TEXT(MOD(D18*12,1),"0/16")&" inches"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Doug Kanter]

I would like to write a formula in excel, without using macros, to convert
decimal feet to feet-inch-sixteenths.

Can someone help me ?

Are you able to write the formula on paper first?

 

[Toppers]

Decimal value in A1, formula in B1:


=INT(A1) & " ft " & TEXT(MOD(A1,1)*12, "# ??/16") & "in"

A1=2.345 (ft)

B1=2 ft 4 2/16 in

HTH

:

 

[Bob Phillips]

Whilst I concede that yours is much better than mine, it can still be
improved upon <vbg>

=INT(A1) & " ft " & TEXT(MOD(A1,1)*12, "# ??/16 ""in""")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Toppers]

Bob,
I'm flattered!

Whilst I concede that yours is much better than mine, it can still be
improved upon <vbg>

=INT(A1) & " ft " & TEXT(MOD(A1,1)*12, "# ??/16 ""in""")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[tv]

Thamk you for this formula
however, i would like my result to read in the following format (ex: 4-6-8)

can you help me tune the formula

thanks

 

[Ron Rosenfeld]

Thamk you for this formula
however, i would like my result to read in the following format (ex: 4-6-8)

can you help me tune the formula

thanks

Here's one way:

=INT(A1)&"-"&INT(MOD(A1,1)*12)&"-"&ROUND(MOD(MOD(A1,1)*12,1)*16,0)


--ron

 

[John A]

I found your question very similar to one our school nurse has posed: When
our gym teachers measure students they report students' height in inches only
ie 61". Might their be a formula that would convert 61 inches (61") to 5' 1"?

 

[Ron Rosenfeld]

I found your question very similar to one our school nurse has posed: When
our gym teachers measure students they report students' height in inches only
ie 61". Might their be a formula that would convert 61 inches (61") to 5' 1"?

Assuming whole inches only:

=INT(A1/12)&" ft, "&MOD(A1,12)&" inches"

or,

=INT(A1/12)*100+MOD(A1,12)

and custom format:

Format/Cells/Number/Custom Type: 0' 00\"

If you want to report to tenths of an inch, change the Custom format to
0' 00.0\"


--ron

 

[John A]

Do I simply enter the formula you recommended into the formula bar in cell A1
(example) and then try entering an inches number 55 or 55" (in cell A1) and
it should convert to 4'7"?

 

[John A]

Ron: I didn't read your complete response... I will try it.

 

[Gord Dibben]

Enter the formula in B1 then enter your number 55 in A1


Gord Dibben MS Excel MVP

 

[John A]

clarification request:
Using your first formula... would the entire formula including the
characters beyond the comma (,) go into the formula bar with cell A1
selected? Also, is there a space after the comma followed by
"&MOD(A1,12)&"inches"?

 

[John A]

I'll try it Gord.
--
Thanks for your help

Enter the formula in B1 then enter your number 55 in A1


Gord Dibben MS Excel MVP

 

[John A]

It worked!!! Fabulous !!!! Thanks

 

[Ron Rosenfeld]

clarification request:
Using your first formula... would the entire formula including the
characters beyond the comma (,) go into the formula bar with cell A1
selected?

No. The formula refers to cell A1 which would be where you put your data. If
you have a formula in the cell, and then overwrite it with data, you no longer
have the formula.

But of course you use the entire formula, if you want the result in feet and
inches.

Also, is there a space after the comma followed by "&MOD(A1,12)&"inches"?
Yes.

--ron

 

[John A]

THe space thing was holding me up. I finally tried it with spaces and it
worked. I wasn't aware spaces were allowed in Excel formulas. 'ya learn
something everyday. Many thanks

 

[Ron Rosenfeld]

THe space thing was holding me up. I finally tried it with spaces and it
worked. I wasn't aware spaces were allowed in Excel formulas. 'ya learn
something everyday. Many thanks

You're welcome.
The space is merely to "make it look good". It is entirely optional.
--ron

 

[John A]

I am in dire need of a How To Question regarding this Discussion group. I
would like to pose a question but when I click the New>Questions button
nothing happens... no window opens for me to enter my question it... that's
why I am piggy-backing on this post...