display feet and inches

[Guest]

Is there any way in excel to display feet and inches? I can get decimal feet
and even fractional feet ie. 3 1/3. Is there any way to multiply the
fraction portion of that by 12 to get it to spit out 4? Even if it ends up
in two differnet cells, I can get another cell to read 3 and the next cell to
read 4, and squash them together to look like 3'-4".

 

[Daniel CHEN]

Use the following formula:
=INT(A1)&"'-"&ROUND(((A1-INT(A1))*12),0) & CHAR(34)

Suppose you have 15.92 in cell A1, you will have 15'-11" from the above
formula. The formula rounds to the nearest inch.

The following one is much more complex, but it converts the decimal feet to
ft-in and fractional inch (64 based)
12.12345 ft will be converted to 12'-1 31/64".

=INT(A1)&"'"&"-"&INT(12*(A1-INT(A1)))&IF((A1-INT(A1))*12-INT((A1-INT(A1))*12)>1/128,"
","")&IF(ISODD(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*64+0.5),INT(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*64+0.5)&"/64",IF(ISODD(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*32+0.5),INT(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*32+0.5)&"/32",IF(ISODD(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*16+0.5),INT(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*16+0.5)&"/16","")))
&IF(OR(ISODD(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*64+0.5),ISODD(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*32+0.5),ISODD(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*16+0.5)),"",IF(ISODD(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*8+0.5),INT(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*8+0.5)&"/8",IF(ISODD(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*4+0.5),INT(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*4+0.5)&"/4",IF(ISODD(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*2+0.5),INT(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*2+0.5)&"/2",""))))&CHAR(34)

You can download a utility program from
http://www.xldatasoft.com/downloads/Conversion.xls

--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download
Free Excel / VBA Training Materials is Available for Downloand
=================================

 

[Guest]

If the number of feet (3 1/3) is in a1, then =INT(A1) & "'-" & 12*MOD(A1,1) &
""""
will display as you described at the end.
--Bruce