|
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. |
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. |
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. |
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),"",")") |
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 |
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? |
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),"",")") |
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),"",")") |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 05:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com