Microsoft Office Tutorials and References

In Depth Information

**Rounding Numbers**

The second argument for the ROUND function can also be negative. In such a case, the number is

rounded to the left of the decimal point. The following formula, for example, returns 120.00:

=ROUND(123.37,–1)

The ROUND function rounds either up or down. But how does it handle a number such as 12.5,

rounded to no decimal places? You’ll find that the ROUND function rounds such numbers away

from zero. The formula that follows, for instance, returns 13.0:

=ROUND(12.5,0)

The next formula returns –13.00 (the rounding occurs away from zero):

=ROUND(–12.5,0)

To force rounding to occur in a particular direction, use the ROUNDUP or ROUNDDOWN

functions. The following formula, for example, returns 12.0. The value rounds down.

=ROUNDDOWN(12.5,0)

The formula that follows returns 13.0. The value rounds up to the nearest whole value.

=ROUNDUP(12.43,0)

Rounding to the nearest multiple

The MROUND function is useful for rounding values to the nearest multiple. For example, you can

use this function to round a number to the nearest 5. The following formula returns 135:

=MROUND(133,5)

Rounding currency values

Often, you need to round currency values. For example, you may need to round a dollar amount

to the nearest penny. A calculated price may be something like $45.78923. In such a case, you’ll

want to round the calculated price to the nearest penny. This may sound simple, but there are

actually three ways to round such a value: