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

=ROUND(G21,–2).

Figure 3-20

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

ROUNDDOWN

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

and Digits.

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.