Working with fractions and mixed numbers in Excel

Forum to talk about all the latest technologies - ins and outs!!
Post Reply
Admin
Site Admin
Posts: 325
Joined: Tue Sep 25, 2007 12:41 pm

Working with fractions and mixed numbers in Excel

Post by Admin »

It's just little bit tricky to enter fractions and mixed numbers in Excel. Just that. Let's make it easy.


Fractions

When you enter any fraction like 5/6 or 7/8 in your cell, Excel interprets these fractions as date and automatically format the cell as a date.


To enter a simple fraction:

first enter 0 and a space (after entering 0 hit the spacebar)

now enter your fraction number – so it will be like 0 5/6 (as an example)

hit Enter. It's done.


Image


The decimal value of the fraction is shown in the formula bar.


Image




Fractions with more than one digit


In the above example we were dealing with fractions up to one digit, so what about fractions with two or more digits like 11/14 or 217/867?


To enter these fraction numbers you have to go to Format Cells, there under Fraction you can select the type – like Up to two digits for 11/14 or Up to three digits for 217/867.


Image



However, if you enter 33/51 in your cell even you have selected the fraction up to two digits, Excel will round the fraction to 11/17 but will give you alwasys the right decimal values (you can check it on the formula bar)

To fix this problem, select the cell where you want to enter this fraction, right click > go to Format Cells.

Here, under category Custom, change # ??/?? to # 33/51. Now your cell will show exactly this value.



Image


So while working with fractions, you will often need to play with cell formatting, if necessary customising it. That's all.



Mixed numbers

To enter any mixed number like 2 5/8:

enter the first number (here 2),

then hit the Spacebar, now enter the fraction (here 5/8 ),

Press Enter. Done!
Post Reply