Since Version 2013, Excel has offered 15 functions that round data in different ways.
The first table describes each function briefly. The second table summarizes their key features.
|Excel Rounding Functions and Descriptions|
|CEILING||Rounds a number up to the nearest multiple of significance, making it larger.|
|CEILING.MATH||Rounds a number up to the nearest integer or multiple of significance. Optionally rounds away from zero.|
|CEILING.PRECISE||Rounds a number up to the nearest integer or multiple of significance. If significance is zero, returns zero.|
|EVEN||Rounds a number up to the nearest even integer|
|FLOOR||Rounds number towards zero, to the nearest multiple of significance.|
|FLOOR.MATH||Rounds a number down to the nearest multiple of significance. Optionally rounds towards zero.|
|FLOOR.PRECISE||Rounds a number down to the nearest multiple of significance.
This topic describes the minor differences between the FLOOR.PRECISE and FLOOR functions.
|INT||Rounds a number down to the nearest integer.|
|ISO.CEILING||Rounds a number up to the nearest multiple of significance. If the significance is zero, returns zero.|
|MROUND||Rounds a number to the desired multiple.|
|ODD||Rounds a number up to the nearest odd integer.|
|ROUND||Rounds a number to a specified number of digits.|
|ROUNDDOWN||Rounds a number towards zero.|
|ROUNDUP||Rounds a number away from zero.|
|TRUNC||Truncates a number to an integer, which rounds the number towards zero if it’s not already an integer.|
Excel Rounding Methods
Excel “rounds” numbers in two ways. First, it can round up or down. For example, rounding up 6.1 gives us 7. And rounding up -6.1 gives us -6. Round down goes in the opposite direction.
Second, numbers can round towards zero or away from zero. When positive numbers are rounded towards zero, they get smaller; when negative numbers are rounded towards zero, they get larger. (That is, for example, -6 is larger than -7.)
Although Microsoft uses “round up” and “round down” to describe both types of rounding, the following table uses “up” or “down” to describe the first type of rounding. It uses
arrows to indicate towards or away from zero. And “opt” means optional.
|Excel’s Rounding Functions Summarized|
|Rounding Direction||Type of Rounding|
|Function||Version||Up||Down||→ 0 ←||← 0 →||Nearest||Integer||Multiple||#Digits|