M
Mark Main
If you've ever wanted to display Feet and Inches with Fractions of an
Inch, then this formula is for you. For example, this will change a
value of 15.125 to be 1' 3-1/8"
Type a number in A1, and paste this formula into B1:
=IF(A1<0,"("," ") & IF(TRUNC(ABS(ROUND(A1*16,0)/16)/
12,0)<1,"",TRUNC(ABS(ROUND(A1*16,0)/16)/12,0) & "'" & IF(MOD(ABS(A1),
12)=0, ""," ")) & IF(TRUNC(ABS(ROUND(A1*16,0)/
16)-12*TRUNC(ABS(ROUND(A1*16,0)/16)/12,0))>=1,
TRUNC(ABS(ROUND(A1*16,0)/16)-12*TRUNC(ABS(ROUND(A1*16,0)/16)/12,0)) &
IF(MOD(ABS(A1),1)=0,"","-"),"") & IF(MOD(A1,1)=0,"",MOD(ABS(A1),1)*16/
GCD(ROUND(ABS(A1)*16,0),16) & "/" & 16/ABS(GCD(ROUND(ABS(A1)*16,0),
16))) & IF(MOD(ABS(A1),12)=0,"","""") & IF(A1<0,")"," ")
It will reformat the number into Feet, Inches, and Fractions of
1/16". It will round the number to the nearest sixteenth inch.
Notice that it also places parentheses () around negative numbers.
If you want something rather than 16th of an inch, simply do a mass
change of 16 to 8ths or whatever you want. Be sure to leave the 12's
alone, they are for Feet.
Hope that it's useful to someone out there.
Mark
Inch, then this formula is for you. For example, this will change a
value of 15.125 to be 1' 3-1/8"
Type a number in A1, and paste this formula into B1:
=IF(A1<0,"("," ") & IF(TRUNC(ABS(ROUND(A1*16,0)/16)/
12,0)<1,"",TRUNC(ABS(ROUND(A1*16,0)/16)/12,0) & "'" & IF(MOD(ABS(A1),
12)=0, ""," ")) & IF(TRUNC(ABS(ROUND(A1*16,0)/
16)-12*TRUNC(ABS(ROUND(A1*16,0)/16)/12,0))>=1,
TRUNC(ABS(ROUND(A1*16,0)/16)-12*TRUNC(ABS(ROUND(A1*16,0)/16)/12,0)) &
IF(MOD(ABS(A1),1)=0,"","-"),"") & IF(MOD(A1,1)=0,"",MOD(ABS(A1),1)*16/
GCD(ROUND(ABS(A1)*16,0),16) & "/" & 16/ABS(GCD(ROUND(ABS(A1)*16,0),
16))) & IF(MOD(ABS(A1),12)=0,"","""") & IF(A1<0,")"," ")
It will reformat the number into Feet, Inches, and Fractions of
1/16". It will round the number to the nearest sixteenth inch.
Notice that it also places parentheses () around negative numbers.
If you want something rather than 16th of an inch, simply do a mass
change of 16 to 8ths or whatever you want. Be sure to leave the 12's
alone, they are for Feet.
Hope that it's useful to someone out there.
Mark