Convert inches & display as Feet Inches and Fractions -- BUG FREE

[Mark Main]

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(A1>0,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(ROUND(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
(A1>0,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.

 

[Peo Sjoblom]

http://www.mvps.org/dmcritchie/excel/fractex1.htm

--


Regards,


Peo Sjoblom

 

[Shane Devenshire]

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

 

[Ron Rosenfeld]

Mark Main wrote:

=IF(ROUND(ABS(A1)*16,0)=ABS(A1)*16,"","~") & IF(OR(A1>0,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(ROUND(ABS(A1)*16,0)
<>0,INT(MOD(INT(

'Excel 2003 flags error here ):
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
(A1>0,ROUND(ABS(A1)*16,0)=0),"",")")

After pasting in the formula, delete all the <LF>'s and it should work OK.
--ron

 

[Shane Devenshire]

Hi,

I got it to work in 2007 after removing the CR's and letting Excel make a
correction some where? Here is the final working formula, which I modified a
little bit:

=IF(ROUND(ABS(C34)*16,0)=ABS(C34)*16,"","~")&IF(OR(C34>0,ROUND(ABS(C34)*16,0)=0),"","(")&TRIM(IF(INT(ROUND(ABS(C34)*16,0)/192)=0,"",INT(ROUND(ABS(C34)*16,0)/192)&"'
")&IF(AND(ROUND(ABS(C34)*16,0)<>0,MOD(ROUND(ABS(C34)*16,0),192)=0),"",TRIM(IF(AND(ROUND(ABS(C34)*16,0)<>0,INT(MOD(INT(ROUND(ABS(C34)*16,0)),192)/16)=0),"",INT(MOD(INT(ROUND(ABS(C34)*16,0)),192)/16)&IF(MOD(INT(ROUND(ABS(C34)*16,0)),16)=0,"","-"))&IF(MOD(INT(ROUND(ABS(C34)*16,0)),16)=0,"",TRIM(TEXT(MOD(INT(ROUND(ABS(C34)*16,0)),16)/16,"??/??")))&"""")))&IF(OR(C34>0,ROUND(ABS(C34)*16,0)=0),"",")")

Anytime I see a formula of this length i'm tempted to use VBA.

Cheers,
Shane Devenshire

 

[Ron Rosenfeld]

I deleted non-printing characters first in a text editor, then pasted in
a cell. I got the same result editing the paste in Excel.

Did it work for you?

It worked fine for me, just deleting the line feeds in the Excel function bar.

Could you have run into a nesting limit with a pre-2007 version of Excel?

By the way, I have found that when breaking formulas onto multiple lines, I
will get an error if I try to separate the function name from the subsequent
"(".
--ron

 

[Ron Rosenfeld]

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(A1>0,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(ROUND(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
(A1>0,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.

Perhaps this shorter version would work also:

=IF(ABS(A1)=MROUND(ABS(A1),1/16),"","~")&IF(A1<0,"(","")&IF(
INT(ABS(A1/12))>=1,INT(ABS(A1/12))&"' ","")&TRIM(TEXT(
MROUND(MOD(ABS(A1),12),1/16),"#-#/##\"""))&IF(A1<0,")","")

1. This requires the Analysis tool Pak to be installed, or the use of Excel
2007. If it is not installed (see Excel HELP for how to do that), then the
MROUND function calls should be replaced with:

ROUND(num*16,0)/16

so:

=IF(ABS(A1)=ROUND(ABS(A1)*16,0)/16,"","~")&IF(A1<0,"(","")&IF(
INT(ABS(A1/12))>=1,INT(ABS(A1/12))&"' ","")&TRIM(TEXT(
ROUND(MOD(ABS(A1),12)*16,0)/16,"#-#/##\"""))&IF(A1<0,")","")


2. This formula gives a result of, for example:

24 --> 2' 0"

whereas yours gives

24 --> 2'

This seems inconsistent to me, since both give

0 --> 0"

but mine could be changed if that is an issue.
--ron

 

[Ron Rosenfeld]

Perhaps this shorter version would work also:

=IF(ABS(A1)=MROUND(ABS(A1),1/16),"","~")&IF(A1<0,"(","")&IF(
INT(ABS(A1/12))>=1,INT(ABS(A1/12))&"' ","")&TRIM(TEXT(
MROUND(MOD(ABS(A1),12),1/16),"#-#/##\"""))&IF(A1<0,")","")

1. This requires the Analysis tool Pak to be installed, or the use of Excel
2007. If it is not installed (see Excel HELP for how to do that), then the
MROUND function calls should be replaced with:

ROUND(num*16,0)/16

so:

=IF(ABS(A1)=ROUND(ABS(A1)*16,0)/16,"","~")&IF(A1<0,"(","")&IF(
INT(ABS(A1/12))>=1,INT(ABS(A1/12))&"' ","")&TRIM(TEXT(
ROUND(MOD(ABS(A1),12)*16,0)/16,"#-#/##\"""))&IF(A1<0,")","")


2. This formula gives a result of, for example:

24 --> 2' 0"

whereas yours gives

24 --> 2'

This seems inconsistent to me, since both give

0 --> 0"

but mine could be changed if that is an issue.
--ron

To change mine to give what seems to be the same output as yours, with regard
to the 0" issue:

=IF(A1=0,0&"""",IF(ABS(A1)=MROUND(ABS(A1),1/16),"","~")&IF(A1<0,"(","")&IF(
INT(ABS(A1/12))>=1,INT(ABS(A1/12))&"' ","")&TRIM(TEXT(
MROUND(MOD(ABS(A1),12),1/16),"#-#/##\"";;"))&IF(A1<0,")",""))

or

=IF(A1=0,0&"""",IF(ABS(A1)=ROUND(ABS(A1)*16,0)/16,"","~")&IF(A1<0,"(","")&IF(
INT(ABS(A1/12))>=1,INT(ABS(A1/12))&"' ","")&TRIM(TEXT(
ROUND(MOD(ABS(A1),12)*16,0)/16,"#-#/##\"";;"))&IF(A1<0,")",""))



--ron

 

[Ron Rosenfeld]

PLEASE IGNORE THIS.

=IF(A1=0,0&"""",IF(ABS(A1)=MROUND(ABS(A1),1/16),"","~")&IF(A1<0,"(","")&IF(
INT(ABS(A1/12))>=1,INT(ABS(A1/12))&"' ","")&TRIM(TEXT(
MROUND(MOD(ABS(A1),12),1/16),"#-#/##\"";;"))&IF(A1<0,")",""))

or

=IF(A1=0,0&"""",IF(ABS(A1)=ROUND(ABS(A1)*16,0)/16,"","~")&IF(A1<0,"(","")&IF(
INT(ABS(A1/12))>=1,INT(ABS(A1/12))&"' ","")&TRIM(TEXT(
ROUND(MOD(ABS(A1),12)*16,0)/16,"#-#/##\"";;"))&IF(A1<0,")",""))



--ron

PLEASE IGNORE THE ABOVE
--ron

 

[Ron Rosenfeld]

To change mine to give what seems to be the same output as yours, with regard
to the 0" issue:

=IF(A1=0,0&"""",IF(ABS(A1)=MROUND(ABS(A1),1/16),"","~")&IF(A1<0,"(","")&IF(
INT(ABS(A1/12))>=1,INT(ABS(A1/12))&"' ","")&TRIM(TEXT(
MROUND(MOD(ABS(A1),12),1/16),"#-#/##\"";;"))&IF(A1<0,")",""))

or

=IF(A1=0,0&"""",IF(ABS(A1)=ROUND(ABS(A1)*16,0)/16,"","~")&IF(A1<0,"(","")&IF(
INT(ABS(A1/12))>=1,INT(ABS(A1/12))&"' ","")&TRIM(TEXT(
ROUND(MOD(ABS(A1),12)*16,0)/16,"#-#/##\"";;"))&IF(A1<0,")",""))

To clarify -- the above formulas do not perform as they should.
--ron