Microsoft Office Tutorials and References

In Depth Information

**Tip 42: 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 42-1 summarizes the Excel rounding functions.

Table 42-1:
Excel Rou
nding Functions

Function

What It Does

CEILING.MATH

Rounds a number up 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.MATH

Rounds a number down to the nearest integer or to the nearest specified

multiple

INT

Rounds a number down 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)