I am trying to display feet and decimal feet in feet and inches. I get odd results depending upon what I am trying to do. I want to display to the nearest 1/8 of an inch and I would like to not wind up with fractions like 4/8 or 2/8
thanks for your help
I'm not sure about your end use, but it can be done if you split the output data into 3 columns (whole feet, whole inches, fractional inches). Use the TRUNC() command to split the whole number of from the decimal. Use the MROUND() command to round to a fraction. For example to round to the nearest 1/8, MROUND(A1,0.125). Then create a custom number format for the fractional inches that have been rounded. Use the following quoted format "??/??".
Excel: Decimal Feet to Feet - Inches & fraction
Assume the foot decimal value is in cell A1 (positive or negative values allowed)
The inches fraction denominator, 8 for 1/8, is in cell B1 (could use 2, 4, 8, 16 etc)
The result is in cell C1
Enter the following formula in cell C1[pre]
=TEXT(TRUNC(A1),"0")&" - "&TEXT(TRUNC((12*ABS(A1-TRUNC(A1)))),"0")
&TEXT(ROUND(((12*ABS(A1-TRUNC(A1)))
-TRUNC((12*ABS(A1-TRUNC(A1)))))*B1,0)/B1,"??/??;;""""")[/pre]
Setting the fraction denominator can be global by changing B1 to $B$1 in the formula in cell C1[pre]
=TEXT(TRUNC(A1),"0")&" - "&TEXT(TRUNC((12*ABS(A1-TRUNC(A1)))),"0")
&TEXT(ROUND(((12*ABS(A1-TRUNC(A1)))
-TRUNC((12*ABS(A1-TRUNC(A1)))))*$B$1,0)/$B$1,"??/??;;""""")[/pre]
Or, it could be done in the formula without having to reference a cell. For 1/8, B1 can be changed to 8:[pre]
=TEXT(TRUNC(A1),"0")&" - "&TEXT(TRUNC((12*ABS(A1-TRUNC(A1)))),"0")
&TEXT(ROUND(((12*ABS(A1-TRUNC(A1)))
-TRUNC((12*ABS(A1-TRUNC(A1)))))*8,0)/8,"??/??;;""""")[/pre]
Note in the formulae above, the carriage return characters must be removed before pasting to a spread sheet. There is a carriage return before the lines beginning with[pre]
&TEXT(ROUND((([/pre] and[pre]
-TRUNC((12[/pre]
The carriage returns are inserted above to accommodate Beerleg posting protocols.
I wrote one of these several years ago, dug it up and have posted the XLS to my Dropbox account:
https://dl.dropboxusercontent.com/u/30460571/ConvertDecimal.xls
I'll leave the file up there for a few weeks. You can place a standard ASCII file in the Input tab and see the results in the Output tab and establish the results to the nearest fractional inch in each of the "Calc" tabs (also provided for troubleshooting).
THANKS FOR YOUR HELP LADD
Excel: Decimal Feet to Feet - Inches & fraction
thanks for your help I REALLY APPRECIATED the various coding options great stuff
thanks again
