Excel Rounding Functions

Excel provides us with a total of thirteen rounding functions, so it's no wonder that many users don't know which of these to use when they want to round a number in Excel.

Therefore, we have produced the following three tables, which split the Excel rounding functions into groups, and describe the behaviour of each of the functions. It is hoped that these tables will help you to decide which of the rounding functions you need to perform your specific task.


Functions for Rounding a Number to an Integer Value

The following three functions take a single number argument and round this value to an integer.

Function Rounds To Direction of Rounding
General Rule Positive Numbers Negative Numbers
INT Integer below down down (less positive) down (more negative)
EVEN next even number away from zero up (more positive) down (more negative)
ODD next odd number away from zero up (more positive) down (more negative)


Functions for Rounding a Number to a Specified Number of Decimal Places

As arguments, the following rounding functions all receive a number to be rounded and a num_digits argument, which specifies the number of decimal places to round to.

The num_digits argument is an integer which, if positive, defines the number of places after the decimal point, that the number should be rounded to. If the num_digits argument is negative, this specifies a number of places to the left of the decimal point.

Function Rounds To Direction of Rounding
General Rule Positive Numbers Negative Numbers
ROUND supplied no.
decimal places
up or down
(to closest value)
up or down
(to closest value)
up or down
(to closest value)
ROUNDUP supplied no.
decimal places
away from zero up (more positive) down (more negative)
ROUNDDOWN
or TRUNC
supplied no.
decimal places
towards zero down (less positive) up (less negative)


Functions for Rounding a Number to a Supplied Multiple of Significance

As arguments, the following Excel rounding functions all receive a number to be rounded and a multiple of significance.

The functions are generally designed to work with a multiple of significance that has the same arithmetic sign as the number to be rounded. If the multiple of significance is the opposite arithmetic sign to the number to be rounded, some of the functions return the Excel #NUM! error.

The functions are summarised in the table below.

Function Rounds To Direction of Rounding
General Rule Positive Numbers
(with positive mult. signif.)
Negative Numbers
(with negative mult. signif.)
Positive Numbers
(with negative mult. signif.)
Negative Numbers
(with positive mult. signif.)
CEILING supplied
mult. signif.
away from zero up (more
positive)
down (more
negative)
#NUM!

up (less
negative)

(or #NUM! in
Excel 2007
or earlier)

CEILING.PRECISE
or ISO.CEILING
supplied
mult. signif.
up up (more
positive)
up (less
negative)
up (more
positive)
up (less
negative)
FLOOR supplied
mult. signif.
towards
zero
down (less
positive)
up (less
negative)
#NUM!

down (more
negative)

(or #NUM! in
Excel 2007
or earlier)

FLOOR.PRECISE supplied
mult. signif.
down down (less
positive)
down (more
negative)
down (less
positive)
down (more
negative)
MROUND supplied
mult. signif.
up or down up or down (to closest multiple) up or down (to closest multiple) #NUM! #NUM!

The different types of rounding and the Excel rounding functions are discussed further Microsoft Office Website

Return to the Excel Math Functions Page

Return to the ExcelFunctions.net Home Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2013 ExcelFunctions.net