Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default Convert inches & display as Feet Inches and Fractions -- BUG FREE

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.

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 964
Default Convert inches & display as Feet Inches and Fractions -- BUG FREE

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

--


Regards,


Peo Sjoblom

"Mark Main" wrote in message
...
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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default Convert inches & display as Feet Inches and Fractions -- 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(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.


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 915
Default Convert inches & display as Feet Inches and Fractions -- BUGFREE

Mark Main wrote:

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

'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
(A10,ROUND(ABS(A1)*16,0)=0),"",")")
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Convert inches & display as Feet Inches and Fractions -- BUG FREE

On Tue, 25 Nov 2008 20:39:05 -0500, smartin wrote:

Mark Main wrote:

=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(R OUND(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
(A10,ROUND(ABS(A1)*16,0)=0),"",")")


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


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 915
Default Convert inches & display as Feet Inches and Fractions -- BUGFREE

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


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?
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default Convert inches & display as Feet Inches and Fractions -- BUG FREE

Looks as if the format exceeds Excel 2003's function nesting limit.
--
David Biddulph

"smartin" wrote in message
...
Mark Main wrote:

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

'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
(A10,ROUND(ABS(A1)*16,0)=0),"",")")



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default Convert inches & display as Feet Inches and Fractions -- BUG F

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 (C340,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(C3 4)*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(C340,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" wrote in message
...
Mark Main wrote:

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

'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
(A10,ROUND(ABS(A1)*16,0)=0),"",")")




  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Convert inches & display as Feet Inches and Fractions -- BUG FREE

On Tue, 25 Nov 2008 21:44:13 -0500, smartin 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
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Convert inches & display as Feet Inches and Fractions -- BUG FREE

On Tue, 25 Nov 2008 15:48:47 -0800 (PST), 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(R OUND(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.



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


  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Convert inches & display as Feet Inches and Fractions -- BUG FREE

On Wed, 26 Nov 2008 08:46:29 -0500, Ron Rosenfeld
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
  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Convert inches & display as Feet Inches and Fractions -- BUG FREE

PLEASE IGNORE THIS.


On Wed, 26 Nov 2008 14:47:00 -0500, Ron Rosenfeld
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
  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Convert inches & display as Feet Inches and Fractions -- BUG FREE

On Wed, 26 Nov 2008 14:47:00 -0500, Ron Rosenfeld
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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I use feet and inches (including fractions of inches) MRZ Excel Worksheet Functions 2 September 18th 08 10:35 PM
Seperating Feet, inches and fractions JDay Excel Discussion (Misc queries) 6 January 14th 08 09:40 PM
Converting from feet, inches and fractions to inches and decimal p Dee Setting up and Configuration of Excel 5 September 18th 07 04:18 PM
Feet, inches and fractions to inches and dec.....reposting for Pet Dee Setting up and Configuration of Excel 1 September 18th 07 04:02 PM
is there a macro that will convert from inches to feet and inches cable guy Excel Discussion (Misc queries) 1 June 20th 05 07:29 PM


All times are GMT +1. The time now is 11:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"