# Microsoft Office Application Help - Excel Help forum > Excel General >  >  convert fractions to decimals and decimal to fraction

## lnjr

I know that if I enter =3/4 in a cell it will give me .75 but is there is formula to convert fractions into decimals or is this the best way to do it?  I was also wondering if there is some way to convert the decimal amount to a fraction

for measurement conversions I know that 
=B3*25.4 will convert a decimal to mm and so will the formula =CONVERT(E4,"in","mm")

and to convert mm to a decimal a few formula that work  are
=CONVERT(H3,"mm","in")
=MROUND(H4/25.4, 1/8)
=H5/25.4
=INT(H6/25.39999918*8)/8


But is there a way to convert a fraction to the decimal and is there a way to convert mm to a fraction instead of the decimal equivalent of the fraction?
or maybe a formula to convert mm to inches but instead of the result being in decimal format it would be in fraction format?

----------


## shg

Just format the number as a fraction: Format > Cells > Number tab, choose _Fraction_ in the Category box.

----------


## lnjr

I didn't realize (or think to try) simply entering the figure as a fraction and have the formula work.  As well as simply formatting for fractions in the mm to inches formula and have it work.

Thanks So Very Much.

(I can't believe I didn't think to try that before asking!!!)


Thanks Again!


Is there a way (formula) for when it is in fractions to convert to decimals and/or vice versa?  (if a column shows items in fractions have the next row show the answer in decimals?)

----------


## shg

> Is there a way (formula) for when it is in fractions to convert to decimals and/or vice versa? (if a column shows items in fractions have the next row show the answer in decimals?)



Not without getting exotic. Just format each column as you wish.

----------


## lnjr

don't really want to get exotic so thanks for the help.

----------


## GMAN22

Decimals to Fractions. Enter the decimal number in the cell (ex. A1) right click on that cell, next click on Format Cells, next click on the Number Tab, next click on Fraction and under Type click on: Up to one digit (1/4) or 2 digits, etc; etc and then click OK. The Decimal number will convert to a Fraction.

----------


## DarbyII

If you have a numerator or denominator larger than 3 digits you have to choose Format, Cell, Number, Custom, scroll to the bottom and add a sufficient number of "?" to the "???/???" fraction example to cover your fractions.

----------


## rfritz

I know I'm a little late to this party but I'm still hoping someone can answer my question here.  

The solutions given in this thread will not work for me (I think).  I have a number (or row of numbers) that go something like this.

23.75
46.75
69.75
92.75
115.75
138.75
161.75
184.75
207.75

However, I want to express this in terms of feet, inch, fraction of inch.  I can get pretty close using the concatenate, left, and mid functions but that only gets me to 1' 11" (for the first value).  I have not been able to take the .75 string and convert that to 3/4 so the actual value of the first number should read 1' 11-3/4".  Any suggestions?  Of course, by now I could just input this in manually (it's a short list) but I'd really like to know if this can be done automatically.  Thanks.

----------


## Emajen

Hey rfritz,

I literally created an account on this forum just to reply to your question. 

I understand your predicament and have a solutions for you, it does not get the job done 100%. There, to my knowledge, is no easy command to do this. 

This has more than one step and I will try and explain them as best as possible.

*Step 1*: If taking a large list of lengths, use the Text to Columns to separate the decimal portion (.75) into its own cell. This option is under the DATA Tab. My suggestion is to pick 'Fixed Width' in order to properly separate the values. Unfortunately this will also leave you with some editing as it can only separate on a straight line. If you use 'Delimited' you have more options, but given the nature of the values it will leave you with just as much manual editing. Its your choice, but I believe 'Fixed Width' is easier for this set of data. This leaves you with two columns. One containing the full inches length (23) and one with the decimal (.75). 

*Step 2*: On the first column use the formula '=convert' from inches to feet. This formula is very easy to use and you seem like a smart guy so I won't go in-depth explaining this. 

*Step 3*: Then you will need to format the second column, the one with the decimals, to fractions. This can be done under the Home tab and again if you're using the concatenate formula I trust you will know how to do this.

*Step 4*: Before you do Step 5, you will need to copy and paste the column with the converted (23) number from Step 2. It should have the value (1.91667) depending on how many decimals you have shown. Paste this into a new column as values. 

*Step 5*: Now you will need to use the Text to Columns option again. Do this for the new column you just pasted values into. It should be the new (1.91667) column (depending on how many decimals you have shown) to separate the value after the period. This will return a column with (1) and a column with (.91667).

*Step 6*: Now, by using the '=convert' formula on the decimal (.91667) half column from feet to inches you can return the value 11. 

*Step 7*This should leave you with three total columns with the values you want. One with (1), one with (11), and one with (3/4).

*Step 8*: Now you can use the concatenate formula to get the format you desire. Yes, it is complicated, but it will get the job done. And if you are familiar with macros then you can set up a somewhat easy solution to your problem. 

*Step 9*: Profit $$$


I hope that helps. If you have any questions feel free to ask. I will be receiving emails about this thread, however I only have MS Excel on my work computer and probably won't be able to successfully answer them until Monday (12/22/14).


-Emajen

----------


## shg

Another way:

Row\Col
A
B
C

1
23.75
1' 11-3/4"
B1:= 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), "-?/#?"), "")
& """"

2
46.5
3' 10-1/2"


3
69.25
5' 9-1/4"


4
92.0625
7' 8-1/16"


5
91.9375
7' 7-15/16"

----------

