Convert decimal to fraction

[BWMagee]

I need to display the decimal value 1.333333333 as fraction 4/3. However, as I'm using them individually in formulas, the 4 and 3 have to be in separate columns. Therefore simply formatting the cell to display as a fraction is of no use.

There are multiple values that need this treatment, some with large decimal values. My current method is this: In column B I round all values to 8 decimal places. In column C I multiply by 1000000000 to display the numerator without decimal places, and in column D I type 1000000000 as the denominator. This gives B = 1333333330 and C= 1000000000. In order to reduce this fraction to its lowest terms, in column E I type =GCD(B,C) which gives a value 10. Dividing B and C by 10, you can see I do not get the answer 4 and 3.

This approach works perfectly on simple decimals, such as 1.25, 1.5, 1.125... but horribly on repeating decimals, such as 1.33333, 1.2727272727, 1.08333333.

Is there a better way to convert decimals to fractions, or a trick so this process works for repeating decimals?

The formula I am using for the values 4, 3 is =LOG(4*3) or =LOG(B*C). I have tried putting the decimal in. Eg. =LOG(1.3333333*1) but it doesn't return satisfactory results. If anyone knows how to use a decimal instead of a fraction in a logarithmic formula like this it would prevent any need to convert decimal to fraction, and would be the ideal answer. But if that is not possible, then I need to find a way to reduce my decimals to lowest fractions. All help is much appreciated.

 

[nemmi69]

Try this

If A1 contains 1.333333
B1 contains
= TEXT(INT(A1/12), "0' ;;")
& IF(INT(MOD(A1, 12)), INT(MOD(A1, 12)), "")
& IF(MROUND(MOD(A1, 1), 1/16), TEXT(MROUND(MOD(A1, 1), 1/16), "-?/#?"), "")
& """"

 

[BWMagee]

Try this

If A1 contains 1.333333
B1 contains
= TEXT(INT(A1/12), "0' ;;")
& IF(INT(MOD(A1, 12)), INT(MOD(A1, 12)), "")
& IF(MROUND(MOD(A1, 1), 1/16), TEXT(MROUND(MOD(A1, 1), 1/16), "-?/#?"), "")
& """"

Thanks for your response. This returns some strange results, such as 1", 2" 1-1/2", 1-5/16", 1-3/4"

 

[nemmi69]

To go further to actually get the numbers

C1 contains =IF(INT(MOD(A1, 12)), INT(MOD(A1, 12)), "")
D1 contains =IF(MROUND(MOD(A1, 1), 1/16), TEXT(MROUND(MOD(A1, 1), 1/16), "?/#?"), "")
E1 contains =MID(D1,1,FIND("/",D1)-1)
F1 contains =TRIM(RIGHT(SUBSTITUTE(D1,"/",REPT(" ",LEN(D1))),LEN(D1)))
G1 (numerator) contains =F1+E1

 

[nemmi69]

Thanks for your response. This returns some strange results, such as 1", 2" 1-1/2", 1-5/16", 1-3/4"

Hmmmm, just seen that

 

[nemmi69]

Right, think this sorts it

If A2 contains 1.333333
B2 contains =INT(A2)
C2 contains =A2-B2
D2 contains =C2 (formatted as a fraction up to 3 digits)
E2 contains =TEXT(D2,"?/#?")
F2 contains =MID(E2,1,FIND("/",E2)-1)
G2 contains =TRIM(RIGHT(SUBSTITUTE(E2,"/",REPT(" ",LEN(E2))),LEN(E2)))
H2 contains =IF(B2=1,G2+VALUE(F2),VALUE(F2))
I2 contains =H2/G2

 

[nemmi69]

Pah, overlooked that the integer could be more than 1

change H2
H2 contains =IF(B2>=1,(G2*VALUE(B2))+VALUE(F2),VALUE(F2))

 

[nemmi69]

Improvement to text conversion as Excel wanted to reduce it

change E1
E1 contains =TEXT(D2,"# ???/???")

 

[BWMagee]

This is absolutely superb nemmi69 thanks so much for the effort!
I made a slight adjustment changing the format from up to 3 digits to custom #####/##### as it suits my needs a little better.

I also changed the divisor formula (G2) to read:
=IF(C2=0,1,TRIM(RIGHT(SUBSTITUTE(E2,"/",REPT(" ",LEN(E2))),LEN(E2))))

This is so in instances where the original number contains no decimal, the divisor returns a value of 1. Previously it was returning 0. But it works fantastically!

 

[UHsoccer]

Stumbled on this threat, it truly borders on the insane to fill my spreadsheet with the following formulas
I actually have thousands of cells that need help....

=TEXT(INT(A18/12), "0' ;;")& IF(INT(MOD(A18, 12)), INT(MOD(A18, 12)), "")& IF(MROUND(MOD(A18, 1), 1/16), TEXT(MROUND(MOD(A18, 1), 1/16), " ?/#?")


Here is a 3 column result A=starting value B formatted fraction to 16, C using the entire formula

Value Entered	Simply formatting the cell using Xcel	Using entire formula
8.73	8 12/16	8 3/4
0.57	9/16	9/16
2 1/2	2 8/16	2 1/2
2.51	2 8/16	2 1/2
1.93	1 15/16	1 15/16

Just wish the column B would reduce values like to 12/16 to 3/4 and 8/16 to 1/2
Any thoughts on that