Microsoft Office Tutorials and References

In Depth Information

**Various Methods of Rounding Numbers**

Various Methods of Rounding Numbers

Rounding numbers is a common task, and Excel provides quite a few functions that round values

in various ways.

You must understand the difference between
rounding
a value and
formatting
a value. When you

format
a number to display a specific number of decimal places, formulas that refer to that

number use the actual value, which might differ from the displayed value. When you
round
a number,

formulas that refer to that value use the rounded number.

Table 111-1 summarizes the Excel rounding functions.

Table 111-1:
Excel Rounding Functions

Function

What It Does

CEILING

Rounds a number up (away from zero) to the nearest specified multiple

DOLLARDE

Converts a dollar price, expressed as a fraction, into a decimal number

DOLLARFR

Converts a dollar price, expressed as a decimal, into a fractional number

EVEN

Rounds up (away from zero) positive numbers to the nearest even integer; rounds

down (away from zero) negative numbers to the nearest even integer

FLOOR

Rounds down (toward zero) a number to the nearest specified multiple

INT

Rounds down a number to make it an integer

MROUND

Rounds a number to a specified multiple

ODD

Rounds up (away from zero) numbers to the nearest odd integer; rounds down (away

from zero) negative numbers to the nearest odd integer

ROUND

Rounds a number to a specified number of digits

ROUNDDOWN

Rounds down (toward zero) a number to a specified number of digits

ROUNDUP

Rounds up (away from zero) a number to a specified number of digits

TRUNC

Truncates a number to a specified number of significant digits

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

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)