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

M

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.
 
S

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
 
S

smartin

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),"",")")
 
R

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),"",")")
Click to expand...

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

smartin

Ron said:
After pasting in the formula, delete all the <LF>'s and it should work OK.
--ron
Click to expand...

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?
 
S

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
 
R

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?
Click to expand...

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
 
R

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.
Click to expand...


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
 
R

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
Click to expand...

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
 
R

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
Click to expand...


PLEASE IGNORE THE ABOVE
--ron
 
R

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,")",""))
Click to expand...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula converts a value to Feet Inches and fractions of an inch 1
Formula converts value into Feet, Inches & Fraction of an Inch 6
Conditional Fromatting and Formulas 5
Calculate the total overlapping time of multiple tasks, excluding non-working times 1
Display a number in feet inches and fractions of and inch 1
Adding and ISERROR to the following (large Formula) 11
Round Function (Significant Figures) for Large Numbers 3
feet and inches 2

Top