M

#### Mark Main

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.