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.

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

--


Regards,


Peo Sjoblom

"Mark Main" <(E-Mail Removed)> wrote in message
news:9f62667c-8c5a-47ef-801a-(E-Mail Removed)...
> 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.
>

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

On Tue, 25 Nov 2008 20:39:05 -0500, smartin <(E-Mail Removed)> wrote:

>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

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

"David Biddulph" wrote:

> Looks as if the format exceeds Excel 2003's function nesting limit.
> --
> David Biddulph
>
> "smartin" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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),"",")")
>
>
>

On Tue, 25 Nov 2008 21:44:13 -0500, smartin <(E-Mail Removed)> wrote:

>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

On Tue, 25 Nov 2008 15:48:47 -0800 (PST), Mark Main <(E-Mail Removed)> 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(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

On Wed, 26 Nov 2008 08:46:29 -0500, Ron Rosenfeld <(E-Mail Removed)>
wrote:

>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

PLEASE IGNORE THIS.


On Wed, 26 Nov 2008 14:47:00 -0500, Ron Rosenfeld <(E-Mail Removed)>
wrote:

>=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

On Wed, 26 Nov 2008 14:47:00 -0500, Ron Rosenfeld <(E-Mail Removed)>
wrote:

>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