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.
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) |
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) |
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.
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) |
CEILING.MATH |
supplied mult. signif. |
up |
up (more positive) |
up (less negative) (can be reversed via mode argument) |
up (more positive) |
up (less negative) (can be reversed via mode argument) |
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) |
FLOOR.MATH |
supplied mult. signif. |
down |
down (less positive) |
down (more negative) (can be reversed via mode argument) |
down (less positive) |
down (more negative) (can be reversed via mode argument) |
MROUND |
supplied mult. signif. |
up or down |
up 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