Excel Rounding Functions

Excel provides us with a total of fifteen 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.

To clarify this, we have produced the following three tables, which split the Excel rounding functions into groups, and describe the behaviour of each of the functions. 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.

FunctionRounds ToDirection of Rounding
General RulePositive NumbersNegative Numbers
INTInteger belowdowndown (less positive)down (more negative)
EVENnext even numberaway from zeroup (more positive)down (more negative)
ODDnext odd numberaway from zeroup (more positive)down (more negative)


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

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.

FunctionRounds ToDirection of Rounding
General RulePositive NumbersNegative Numbers
ROUNDsupplied no.
decimal places
up or down
(to closest value)
up or down
(to closest value)
up or down
(to closest value)
ROUNDUPsupplied no.
decimal places
away from zeroup (more positive)down (more negative)
ROUNDDOWN
or TRUNC
supplied no.
decimal places
towards zerodown (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.

FunctionRounds ToDirection of Rounding
General RulePositive
Numbers
(with positive
mult. signif.)
Negative
Numbers
(with negative
mult. signif.)
Positive
Numbers
(with negative
mult. signif.)
Negative
Numbers
(with positive
mult. signif.)
CEILINGsupplied
mult. signif.
away from zeroup (more
positive)
down (more
negative)
#NUM!up (less
negative)
(or #NUM! in
Excel 2007
or earlier)
CEILING.PRECISE
or ISO.CEILING
supplied
mult. signif.
upup (more
positive)
up (less
negative)
up (more
positive)
up (less
negative)
CEILING.MATHsupplied
mult. signif.
upup (more
positive)
up (less
negative)
(can be
reversed via
mode argument)
up (more
positive)
up (less
negative)
(can be
reversed via
mode argument)
FLOORsupplied
mult. signif.
towards
zero
down (less
positive)
up (less
negative)
#NUM!down (more
negative)
(or #NUM! in
Excel 2007
or earlier)
FLOOR.PRECISEsupplied
mult. signif.
downdown (less
positive)
down (more
negative)
down (less
positive)
down (more
negative)
FLOOR.MATHsupplied
mult. signif.
downdown (less
positive)
down (more
negative)
(can be
reversed via
mode argument)
down (less
positive)
down (more
negative)
(can be
reversed via
mode argument)
MROUNDsupplied
mult. signif.
up or downup or down
(to closest
multiple)
up or down
(to closest
multiple)
#NUM!#NUM!

Note that some of the above functions are only available in recent versions of Excel:


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