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 (or #NUM! in |
|
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 (or #NUM! in |
| 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
