Formula converts a value to Feet Inches and fractions of an inch

[Mark Main]

Paste this formula below into B2 of Excel, and then enter any number
in inches into cell A1.

=IF(A1<0,"("," ") & IF(TRUNC(ROUND(ABS(A1)*16,0)/
16/12,0)<1,"",TRUNC(ROUND(ABS(A1)*16,0)/16/12,0) & "'" &
IF(MOD(ABS(A1),12)=0, ""," ")) & IF(TRUNC(ROUND(ABS(A1)*16,0)/
16-12*TRUNC(ROUND(ABS(A1)*16,0)/16/12,0))>=1,
TRUNC(ROUND(ABS(A1)*16,0)/16-12*TRUNC(ROUND(ABS(A1)*16,0)/16/12,0)) &
IF(MOD(ABS(A1),1)=0,"","-"),"") & IF(MOD(A1,1)=0,"", ROUND(MOD(ABS(A1),
1)*16,0)/GCD(ROUND(MOD(ABS(A1),1)*16,0),16) & "/" & 16/
GCD(ROUND(ABS(A1)*16,0),16)) & IF(MOD(ABS(A1),12)=0,"","""") &
IF(A1<0,")"," ") & IF(ROUND(MOD(ABS(A1),1)*16,0) = MOD(ABS(A1),1)*16,"
","~")


The formula will convert the value into a format like this: 1' 3-5/16"

This formula rounds to the nearest 16th of an inch and if rounding was
required that a tilde ~ will appear on the right side to signify that
it's an approximation rather than being an exact conversion. E.g. 3'
2-3/4"~

Negative numbers are shown inside parentheses. E.g. (1' 10-1/2)

And negative numbers with rounding approximation would be like this:
(2' 4-7/16")~

If you want to round to the nearest 8th of an inch just change every
16 to an 8 in the formula.

I had posted a previous formula a few weeks ago and it had a bug, so I
deleted that message and reposted here.

 

[Mark Main]

Paste thisformulabelow into B2 of Excel, and then enter any number
ininchesinto cell A1.

=IF(A1<0,"("," ") & IF(TRUNC(ROUND(ABS(A1)*16,0)/
16/12,0)<1,"",TRUNC(ROUND(ABS(A1)*16,0)/16/12,0) & "'" &
IF(MOD(ABS(A1),12)=0, ""," ")) & IF(TRUNC(ROUND(ABS(A1)*16,0)/
16-12*TRUNC(ROUND(ABS(A1)*16,0)/16/12,0))>=1,
TRUNC(ROUND(ABS(A1)*16,0)/16-12*TRUNC(ROUND(ABS(A1)*16,0)/16/12,0)) &
IF(MOD(ABS(A1),1)=0,"","-"),"") & IF(MOD(A1,1)=0,"", ROUND(MOD(ABS(A1),
1)*16,0)/GCD(ROUND(MOD(ABS(A1),1)*16,0),16) & "/" & 16/
GCD(ROUND(ABS(A1)*16,0),16)) & IF(MOD(ABS(A1),12)=0,"","""") &
IF(A1<0,")"," ") & IF(ROUND(MOD(ABS(A1),1)*16,0) = MOD(ABS(A1),1)*16,"
","~")

Theformulawill convert the value into a format like this: 1' 3-5/16"

Thisformularounds to the nearest 16th of an inch and if rounding was
required that a tilde ~ will appear on the right side to signify that
it's an approximation rather than being an exact conversion.  E.g. 3'
2-3/4"~

Negative numbers are shown inside parentheses.  E.g.   (1' 10-1/2)

And negative numbers with rounding approximation would be like this:
(2' 4-7/16")~

If you want to round to the nearest 8th of an inch just change every
16 to an 8 in theformula.

I had posted a previousformulaa few weeks ago and it had a bug, so I
deleted that message and reposted here.

I was told that the tilde is more commonly placed on the left side for
approximation and so this fixes that:

=IF(ROUND(MOD(ABS(A1),1)*16,0) = MOD(ABS(A1),1)*16," ","~") &
IF(A1<0,"("," ") & IF(TRUNC(ROUND(ABS(A1)*16,0)/
16/12,0)<1,"",TRUNC(ROUND(ABS(A1)*16,0)/16/12,0) & "'" &
IF(MOD(ABS(A1),12)=0, ""," ")) & IF(TRUNC(ROUND(ABS(A1)*16,0)/
16-12*TRUNC(ROUND(ABS(A1)*16,0)/16/12,0))>=1,TRUNC(ROUND(ABS(A1)*16,0)/
16-12*TRUNC(ROUND(ABS(A1)*16,0)/16/12,0)) & IF(MOD(ABS(A1),
1)=0,"","-"),"") & IF(MOD(A1,1)=0,"", ROUND(MOD(ABS(A1),1)*16,0)/
GCD(ROUND(MOD(ABS(A1),1)*16,0),16) & "/" & 16/GCD(ROUND(ABS(A1)*16,0),
16)) & IF(MOD(ABS(A1),12)=0,"","""") & IF(A1<0,")"," ")