+ Reply to Thread
Results 1 to 10 of 10

convert fractions to decimals and decimal to fraction

  1. #1
    Registered User
    Join Date
    04-16-2008
    Posts
    63

    convert fractions to decimals and decimal to fraction

    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?
    Attached Files Attached Files
    Last edited by lnjr; 02-09-2009 at 12:55 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: convert fractions to decimals and decimal to fraction

    Just format the number as a fraction: Format > Cells > Number tab, choose Fraction in the Category box.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    04-16-2008
    Posts
    63

    Re: convert fractions to decimals and decimal to fraction

    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?)
    Last edited by lnjr; 02-08-2009 at 01:16 PM.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: convert fractions to decimals and decimal to fraction

    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.

  5. #5
    Registered User
    Join Date
    04-16-2008
    Posts
    63

    Re: convert fractions to decimals and decimal to fraction

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

  6. #6
    Registered User
    Join Date
    03-16-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: convert fractions to decimals and decimal to fraction

    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.

  7. #7
    Registered User
    Join Date
    05-13-2012
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: convert fractions to decimals and decimal to fraction

    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.

  8. #8
    Registered User
    Join Date
    08-17-2011
    Location
    camarillo, CA
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: convert fractions to decimals and decimal to fraction

    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.

  9. #9
    Registered User
    Join Date
    12-19-2014
    Location
    Wisconsin, USA
    MS-Off Ver
    2013
    Posts
    4

    Re: convert fractions to decimals and decimal to fraction

    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 7This 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

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: convert fractions to decimals and decimal to fraction

    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"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1