Microsoft Office Tutorials and References

In Depth Information

The following sections provide examples of formulas that use various types of rounding.

Basic rounding formulas

The ROUND function is useful for basic rounding to a specified number of digits. You specify the number of

digits in the second argument for the ROUND function. For example, the formula that follows returns
123.4

(the value is rounded to one decimal place):

=ROUND(123.37,1)

If the second argument for the ROUND function is zero, the value is rounded to the nearest integer. The formula

that follows, for example, returns
123.00:

=ROUND(123.37,0)

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 fol-

lowing 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)

The second argument for MROUND can be a fractional number. For example, this formula rounds the value in

cell A1 to the nearest one-eighth.