Tip #3878 - Excel Works With More Than You Think
Printer Friendly Version | E-Mail This Tip
Excel
Works With More Than You Think
Have you ever found
yourself working in MS Excel and needed to enter fractions? What did you
do?
(Besides have flashbacks
to middle school where you labored endlessly over those dreaded things!)
I know I've made them
into formulas before. Basically, I divide the numerator by the denominator,
add on the whole number and then convert the whole thing to a decimal
in the process.
It works, but only
if I don't need the fractions to be maintained.
Wouldn't it be nice
to know how to work in Excel with the fractions, along with getting your
answers back in fractions?
I find, at least sometimes,
the ability to complete the work in fractions is a plus. So, let's get
to it!
When you need to enter
a mixed number into an Excel cell, simply type this:
- The whole
number
- Followed
by a space
- The fraction
numerator
- A slash
(/)
- The fraction
denominator
What you should see
in the cell for, let's say two and four-fifths, would be:
If you need to see
the decimal equivalent, simply take a look in the formula bar:
Now that we've seen
the basics, let's look at a couple of special cases.
First, let's discuss
the case of simple fractions (fractions without a whole number).
In order to get Excel
to accept simple fractions and actually treat them as fractions, you'll
need to enter 0 as the whole number, then the space,
then the fraction as done above.
If you don't enter
the whole number 0, Excel will turn your fraction into a date and you
don't want that! (For many people, fractions are confusing enough without
throwing unwanted dates into the mix!)
The second special
case I wanted to discuss involves fractions containing three digit numerators
or denominators.
In order to get Excel
to keep the larger numbers, you'll need to format the cells appropriately.
I could get the program
to accept fractions with two digit numerators or denominators, but once
I bumped it up to three digits, the program automatically reduced the
fraction back to a two digit number. This even happened in cases where
the fraction could not be mathematically reduced. It simply approximated
as close at it could to the original entered data.
So, I went looking
in the Format menu, Cells choice (Ctrl
+ 1) and found a solution.
You simply need to
set the fraction type as "Up to three digits"
and then click OK.
Now, you can use fractions
in your formulas, in many cases making the results more exact. I mean,
let's face it, we tend to round the decimals after a few places and Excel
won't do that during the calculations. That is, unless we force it to.
That's it. Excel fractions
made easy!
~ April
Want To Comment On This Tip? Click Here! We'd love to hear from you :-)
Like These Tips? Get 'Em Free In Your E-mail
