On Aug 28, 2:41*pm, Duke Carey
wrote:
With your formula, for 88.28125 i get

*7' 4-4.50008000000003/16"

Here's a slightly shorter formula that*appears* to work a little better

=IF(A1<0,"(","")&INT(ABS(A1)/12)& "'
"&IF(MOD(ABS(A1),12)0,INT(MOD(ABS(A1),12)),"")&IF (MOD(ABS(A1),1)0,"-"&TEX*T(MOD(ABS(A1),1),"??/??")&CHAR(34),""&CHAR(34))&IF(A1<0,")","")

For the same value it returns

7' 4- 9/32"

Interesting exercise



"Mark Main" wrote:
If you've ever wanted to displayFeetandIncheswith 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 intoFeet,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 forFeet.

Hope that it's useful to someone out there.

Mark- Hide quoted text -

- Show quoted text -

Sorry for the delayed response, I got really busy. You're right about
the bug. I fixed it. I like how short your version is... but my goal
was to round to the nearest 16th of an inch and this formula does
that. If the calculated fraction is a rounded approximate then a
tilde will be show on the right side.

Paste this formula below into cell 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,"
","~")


This formula assumes 16ths of an inch rounding accuracy… if you want
8ths an inch just change EVERY occurance of 16 to be an 8.

The formula also provides negative numbers inside parentheses ()