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):
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:
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:
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:
The next formula returns –13.00 (the rounding occurs away from zero):
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.
The formula that follows returns 13.0 . The value rounds up to the nearest whole value.
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:
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.