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)
Search JabSto ::

Custom Search