Decimal to inch fractions

[Davidmin]

Need to convert decimal to inch fractions
IE: 4.5 = 4 1/2 inches

 

[Mat]

You can choose in the cell format to show the number with a fraction on 16...

or add a presonnalized number format : #" " ??/16

But it will always show it on 16... does it matter to have 4/16 instead of 1/4?

Mat

 

[jimrward]

If you take your original ask, all your fractional measurements given can be broken down into the common denominator which is 1/16 so unfortunately 8/16 will be shown by my suggested formula instead of the 1/2.

=INT(A1) & " " & (FLOOR(A1,1/16)-INT(A1))*16 & "/16"

As far as your extended question about two formula, breaking down the above into it's component parts should yield what you are looking for, basically the FLOOR statement rounds it down to the nearest 1/16 which yields a decimal as a multiple of a sixteenth, by multiplying by sixteen we find what that multiple is, the stuff in quotes (") is to make a text representation of a format that excel does not have as standard.

You could make this more versitile by including reference to a cell which holds the accuracy of your measuring stick, as follows

=INT(A1) & " " & (FLOOR(A1,1/F1)-INT(A1))*F1 & "/" & F1

where F1 holds that value, so to work in eighths set F1 to 8

As far as reducing the fractional part to its absolute value, ie 8/16 = 1/2 errrr I need to think about that.

Another thought would be to have 2 cells, one with FLOOR function and the other with CEILING, that would give a range of acceptable tolarances, obviously where the two cells equate to the same value so much the better, either way the error will be dependent on the accuracy of the measuring stick as shown in cell F1 above

Hope this helps.

 

[Davidmin]

=INT(A1) & " " & (FLOOR(A1,1/16)-INT(A1))*16 & "/16"

That works pretty well. Is there a way for it to give me 1/2 instead of 8/16 ?

 

[dolphin]

Try:

=INT(A1/12)&" Foot " & TEXT(MOD(A1,12),"0"&IF(ABS(MOD(A1,12)-ROUND(MOD(A1,12),0))>1/32," 0/"&CHOOSE(ROUND(MOD(MOD(A1,12),1)*16,0),16,8,16,4,16,8,16,2,16,8,16,4,16,8,16),"")) & " Inches"

 

[Yogi Anand]

Hi Davidmin:

I had a bit of play with what you are trying to do as presented in the following ...

y030923h1.xls
	A	B	C	D
1		smallestFractionOnRuler	nextSmallestFractionOnRuler
2		1/16	1/8
3	Number	01/16	01/8
4	1.59	15/8
5	1.25	11/4
6	1.87	17/8
7	3.11	31/8
8	4.37	43/8
9	0.33	03/8
10	15.17	151/8
11	3.3	31/4
12	1.5	11/2
13	3.32	33/8
14	3.33	33/8
15	1.125	11/8
16	3.35	33/8
17	3.36	33/8
18	1.373	13/8
19	1.11	11/8
20	3.39	33/8
21	1/16	0
22	0.11	01/8
23	3.42	33/8
Sheet5a

The formula in cell B4 is to get readings in 1/8 ths and higher

=IF(MOD(TEXT(ROUND($A4/$B$3,0)*$B$3,"0 ?/?"),$B$3)<>0,TEXT(ROUND($A4/$C$3,0)*$C$3,"0 ?/?"),TEXT(ROUND($A4/$C$3,0)*$C$3,"0 ?/?"))

This may not be the ultimate solution -- but in my limited testing it is working for the few cases presented here.

I hope this helps -- otherwise explain a little further and then let us take it from there.

 

[Yogi Anand]

and the following formula in cell B4 will give us in 1/16 ths and higher ...

=IF(MOD(TEXT(ROUND($A4/$B$3,0)*$B$3,"0 ?/??"),$B$3)<>0,TEXT(ROUND($A4/$C$3,0)*$C$3,"0 ?/??"),TEXT(ROUND($A4/$B$3,0)*$B$3,"0 ?/??"))

as depicted in ...

y030923h1.xls
	A	B	C	D
1		smallestFractionOnRuler	nextSmallestFractionOnRuler
2		1/16	1/8
3	Number	01/16	01/8
4	1.59	19/16
5	1.25	11/4
6	1.87	17/8
7	3.11	31/8
8	4.37	43/8
9	0.33	05/16
10	15.17	153/16
11	3.3	35/16
12	1.5	11/2
13	3.32	35/16
14	3.33	35/16
15	1.125	11/8
16	3.35	33/8
17	3.36	33/8
18	1.373	13/8
19	1.11	11/8
20	3.39	33/8
21	1/16	01/16
22	0.11	01/8
23	3.42	37/16
Sheet5a

 

[Mat]

supposing your value in decimal is in the cell C7 :

=SI(MOD((C7-ENT(C7))*16;8)=0;TEXTE(C7;"#"" ""?/2");SI(MOD((C7-ENT(C7))*16;4)=0;TEXTE(C7;"#"" ""?/4");SI(MOD((C7-ENT(C7))*16;2)=0;TEXTE(C7;"#"" ""?/8");TEXTE(C7;"#"" ""??/16"))))


It's another way (well my formula is in french...)

in english it would be something like :

=IF(MOD((C7-ENT(C7))*16,8)=0,TEXT(C7,"#"" ""?/2"),IF(MOD((C7-ENT(C7))*16,4)=0,TEXT(C7,"#"" ""?/4"),IF(MOD((C7-ENT(C7))*16,2)=0,TEXT(C7,"#"" ""?/8"),TEXT(C7,"#"" ""??/16"))))

Don't know if the Function ENT in french is the same in english, maybe it semething like INT, ENT for ENTIER, it gives the Integer of a number rounded down.

 

[Yogi Anand]

Hi Mat:

In the English speaking world, the Excel function for the integer of a number is indeed INT

 

[Legacy 12630]

Alternatively :-

Format the cell as #??/?? and enter the following :-

=MROUND(A1,1/16)

 

[Mat]

Thank you for the confirmation Yogi,

Finally, using your formula, with INT, it is shorter and gives :

=SI(MOD(TEXTE(ENT(16*A4)/16;"0"" ""?/??");1/16)<>0;TEXTE(ENT(8*A4)/8;"0"" ""?/??");TEXTE(ENT(16*A4)/16;"0"" ""?/??"))

=IF(MOD(TEXT(INT(16*A4)/16,"0"" ""?/??"),1/16)<>0,TEXT(INT(8*A4)/8,"0"" ""?/??"),TEXT(INT(16*A4)/16,"0"" ""?/??"))

Oh...

Format the cell as #??/?? and enter the following :-
=MROUND(A1,1/16)

woah that seems to be THE solution! what is the MROUND function in french? I wanna see that!