Hi,

I'll bet that was a lot of fun! I haven't checked it, but good work!

You could replace (12*16) with simply 192 and (16) with 16. Make it a
little shorter and would calculate faster.

Cheers,
Shane Devenshire

"Mark Main" wrote:

For Cell A1 enter a value of inches and use the decimal place to
represent fractions of an inch (e.g. 3.5 for 3-1/2")

For Cell B1 paste in this formula:

=IF(ROUND(ABS(A1)*16,0)=ABS(A1)*16,"","~") & IF(OR(A10,ROUND(ABS(A1)
*16,0)=0),"","(") & TRIM(IF(INT(ROUND(ABS(A1)*16,0)/(12*16))=0,"",INT
(ROUND(ABS(A1)*16,0)/(12*16))&"' ") & IF(AND(ROUND(ABS(A1)*16,0)<0,MOD
(ROUND(ABS(A1)*16,0),(12*16))=0),"",TRIM(IF(AND(RO UND(ABS(A1)*16,0)
<0,INT(MOD(INT(ROUND(ABS(A1)*16,0)),(12*16))/16)=0),"",INT(MOD(INT
(ROUND(ABS(A1)*16,0)),(12*16))/16)&IF(MOD(INT(ROUND(ABS(A1)*16,0)),16)
=0,"","-")) & IF(MOD(INT(ROUND(ABS(A1)*16,0)),16)=0,"",TRIM(TEXT (MOD
(INT(ROUND(ABS(A1)*16,0)),(16))/16,"??/??"))) &""""))) & IF(OR
(A10,ROUND(ABS(A1)*16,0)=0),"",")")


This formula will round to the nearest 16th inch; if you want to round
to 8ths instead, then change EVERY 16 to an 8 and it will work.

This formula will place a tilde ~ on the far left when rounding was
required; it denotes that it's an approximation rather than being
exact. If no rounding was needed, then the display is an exact
representation and no tilde will show up.

Parentheses will be displayed when the number is negative.

A zero will be displayed simply as 0"

I've fully tested this and it works perfectly... My previous two
attempts had a bug when you entered a value like 14.0001... it would
display 1' 2-0/1" This fixes that... so this is bug free.