Microsoft Office Tutorials and References
In Depth Information
Using Other Functions
Let’s take a look at the worksheet if you round
the values, and then add them. As you can see in
Column E, the actual values, which are displayed
with four decimal places, have been rounded to
the nearest penny. So even when you display the
values using only two decimal places (as shown
in Column F), the total is correct. Of course, if
you are interested in every digit of the calculated
sales projections, you should display them fully
so that your totals will look correct.
To round a value to the nearest specified decimal
place, use this syntax: =ROUND(Number,Digits).
For example, to round the value in cell G21 to
the nearest penny, type =ROUND(G21,2).
To round to the nearest whole number, type
=ROUND(G21,0). To round to the left of the
decimal point, use a negative number. For
example, to round to the nearest hundred, type
Your totals can drive you crazy if you forget to round.
In this worksheet, you can see the actual values
(column D), and the values that appear in the
worksheet if you apply a two-decimal format to
those numbers (column C). Let’s pretend for a
second that your worksheet only shows Column
C—Column D is there only so you can see what’s
going on. If you do some quick math, you will
see that the September Sales Projections in
Column C look as if they have been added
incorrectly, since 2147.83+502.18+1865.33+
2044.00+3122.16+1755.86 equals 11,437.36
and not 11,437.37.
Using ROUNDUP and
Rather than rounding to the nearest
speciﬁed digit, you can force Excel to round up
by using the ROUNDUP function instead.
Use the ROUNDDOWN function to force
Excel to round down. Both ROUNDUP
and ROUNDDOWN work the same way as
ROUND, with two arguments, Number
So, here’s the lesson you need to learn: if you
plan on displaying a limited number of decimal
places in your worksheet and not whole numbers,
you might want to use the ROUND function to
adjust each value so that the displayed value is
equal to the actual value used in calculations.