Excel provides a total of fifteen rounding functions, so it is not surprising that many users don't know which of these to use when rounding a number in Excel.
To clarify this, we have produced the following three tables, which split the Excel rounding functions into groups, and summarize the behaviour of each function. These tables will help you to decide which of the rounding functions you need to use to perform your specific task.
The following three functions all round a number 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.
| 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) |
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 were only introduced in recent versions of Excel and so are not available in older versions:
For further details on rounding numbers in Excel, see the Microsoft Office Website