MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Converting feet (in decimals, transit output) into English Ft.-inches


Posted by Wyg on September 10, 2000 6:34 PM

The measuring stick of a surveying transit for (landscape) grading of ground is measured in "decimal" units of feet, inches 0-9, tenths of an inch.
Example 5,3,6 is 5 ft., 3 inches, 6/10ths of an inch.
Is there a formula or a lookup table for easy calculation into English units of ft-in-1/16"?
Also is there a formula for calculating the differences between such numbers -- either within the decimal or within the English format?
I have dozens of such decimal readings to convert and subtract.
Thanks for any help.


Posted by Celia on September 11, 0100 1:01 AM

Wyg
Here’s a way using worksheet formulas.
The formulas are a bit long and of course there is very likely a better way with shorter or easier formulas.
Also, rather than using formulas, it would be better to do it with some UDF’s instead, however……..

Put your data (5,3,6) in A1.

Put the following formula in B1 :-
=SUBSTITUTE(A1,RIGHT(A1,1),"")&TEXT(RIGHT(A1,1)/10,"??/16")
This should produce the result : 5,3,10/16

The data in cells A1 and B1 are text, so cannot be used in calculations.
For calculation purposes, the easiest way is to convert the data into inches with one decimal place.
Put this formula into C1 :-
=LEFT(A1,FIND(",",A1)-1)*12+LEFT(RIGHT(A1,LEN(A1)-FIND(",",A1,1)),FIND(",",RIGHT(A1,LEN(A1)-FIND(",",A1,1)))-1)*1+RIGHT(A1,1)*1/10
This should produce the result : 63.6
Cell C1 can be used in other calculation formulas (summing, subtracting, etc.)

To convert "decimal inches" into the format of the original data (5,3,6), put the following formula in D1 :-
=INT(C1/12)&","&INT(MOD(C1,12))&","&INT((MOD(C1,12)-INT(MOD(C1,12)))*10)

Celia