Similarly, when working with Excel, typing in any fractions will result in an immediate conversion to its decimal equivalent; i.e. 0.500000 in this case.

However, especially in banking, there are times (bond accounting) where you want the information to both be retained in a fractional format AND you don’t want the entered fraction to be reduced (i.e. 9/12 becomes 3/4).

I think that once you get past the 8th grade, there are very few times where you actually use fractions in their “raw” format – i.e., if you see “1/2 price”, your brain automatically converts it to “50% off”.

Similarly, when working with Excel, typing in any fractions will result in an immediate conversion to its decimal equivalent; i.e. 0.500000 in this case.

However, especially in banking, there are times (bond accounting) where you want the information to both be retained in a fractional format AND you don’t want the entered fraction to be reduced (i.e. 9/12 becomes 3/4).

Excel allows you to enter fractions into cells, provided you preface the entry with a zero and a space. Thus, if you wanted to enter a fraction such as 20/32, you would enter 0 20/32 into the cell. (If you just enter 20/32, then Excel  assumes you are entering either a date or text.)

Special thanks to ExcelTips for this information:

When you enter your fraction, Excel does two things during the analysis process. First, it formats the cell as a fraction, based on the number of digits in the denominator of the fraction. In the case of the fraction 20/32, there are two digits in the denominator, so the cell is automatically formatted as a fraction of up to two digits. (See the Number tab of the Format Cells dialog box.)

The second thing that happens is that Excel converts the number into its decimal equivalent. In other words, Excel stores the number internally as 0.625000, which is what you get when you divide 20 by 32. At this point, the fraction no longer exists; the number is a decimal value.

After you enter any value into Excel, it automatically recalculates your worksheet. With the analysis done, and the new value entered, Excel recalculates and redisplays values. Remember — the value in the cell is now 0.625000, and to redisplay the value, Excel sees that it is supposed to use a fraction of up to two digits. The smallest fraction it can do this with is 5/8. Thus, this is what Excel displays in the cell — 5/8 instead of 20/32.

Because of the analysis process that Excel follows, there is no way that you can force Excel to remember your fractions exactly as you entered them. After all, Excel doesn’t store fractions, it stores decimal values. You can, however, change the format used by Excel to display the value in a particular cell. For instance, if you wanted the cell into which you entered 20/32 to display the fraction with 32 as the denominator, then you could follow these steps:

1) Select the cell you want to format.

2) Select “Format Cells”

3) In the “Number” tab, select “Custom” in the “Category” list.

4) The “Type” box needs contain the characters # ??/??

5) Change the contents of the “Type” box to # ??/32 (in this case)

6) Click “Ok”

Again, remember that this only changes the display of the values in Excel, not the actual values themselves — they are still decimal values. The only way to have Excel remember exactly what you entered is to enter the fraction as text (format the cell as Text before making your entry), but there is a drawback to this. Once entered as text, you cannot use the fraction in any calculations.

If this is a problem for your needs, then you may want to consider putting the numerator in one cell and the denominator in another cell. This provides a way to remember what you entered, but still be able to use the numerator and denominator in a formula.

If you need to separate out the numerator from the denominator in two separate cells, consider using this formula (assuming your fraction is in cell “A1”):

For the numerator:  =LEFT(A1,FIND(“/”,A1)-1)

For the denominator:  =RIGHT(A1,LEN(A1)-FIND(“/“,A1))