Microsoft Office Tutorials and References

In Depth Information

**ROUND(), ROUNDDOWN(), ROUNDUP(): Rounding Numbers**

Rounding Numbers

•
Use a rounding function
. This approach gives you more control. For example,

you can round a number
before
you use it in another calculation, and you can

round numbers to a multiple you choose, like 500 or 1,000. The drawback is

that when you use a rounding function, you may lose precision. This doesn’t

happen when you change the number format, which simply tweaks the way the

number is displayed.

With classic overkill, Microsoft includes no fewer than 10 functions designed

specifically for rounding numbers; what follows is a look at the most useful ones.

ROUND(), ROUNDDOWN(), ROUNDUP(): Rounding Numbers

The most basic (and most commonly used) of Excel’s rounding functions is

ROUND(), which rounds a numeric value to whatever level of precision you choose.

The ROUND() function needs two arguments: the actual number you want to

round, and the number of digits you want to keep to the right of the decimal point.

Here’s what it looks like:

ROUND(number_to_round, number_of_digits)

For example, the following formula rounds the number 3.987 to two decimal places.

The result is 3.99.

=ROUND(3.987, 2)

If you specify 0 for the number of digits, then Excel rounds to the nearest whole

number. Interestingly, you can also round to the nearest 10, 100, 1000, and so on by

using negative numbers for the second argument. For example, if you use –2 for the

number of digits, then Excel rounds two digits to the
left
of the decimal place, which

means your number gets rounded to the nearest 100.

Here’s an example:

=ROUND(34655.7, -2)

This formula produces a result of 34,700.

Note:
The ROUND() function always rounds the positive values 1 through 4
down
; 5 through 9 get rounded

up. If you round 1.5 to the nearest whole number, for instance, the result is 2. When dealing with negative

numbers, Excel rounds the digits 5 through 9 down (toward the larger negative value). Similarly, –1 through

–4 get rounded up. For example, –1.5 gets rounded to –2, while –1.4 gets rounded up to –1.

The ROUNDDOWN() and ROUNDUP() functions work similarly to ROUND().

Like ROUND(), they take two arguments: the number you want to round, and the

number of decimal places you want the final, rounded number to use. The difference

is that ROUNDDOWN() always rounds numbers down, while ROUNDUP() always

rounds numbers up.