Rounding Functions
Rounding functions are important to many users of Excel. Formatting a number to display two decimal places in a worksheet gives the appearance of rounding, but Excel still uses the original number in any calculations. This can result in an apparent error in some formula results. The table below displays raw data in column A. Column B displays the same data formatted to display only two decimal places. Column C displays the same data rounded to two decimal places with the ROUND function. The total in B21 is an accurate total, as we can see if we compare it to the total in A21, but it is not an accurate total of the displayed values. Because we rounded first in column C, unseen decimal places are not affecting our total. The total in C21 reflects an accurate sum of the displayed values.
- ROUND
- Syntax: ROUND(number, number of digits)
Rounds a number to a specified number of digits. If number of digits is less than 0, then the number is rounded up that many places to the left of the decimal point.
With the number 613557.188 in cell A1, the following would result from the given formula:
613,557.19 =ROUND(A1,2)
613,557.20 =ROUND(A1,1)
613,557.00 =ROUND(A1,0)
613,560.00 =ROUND(A1,-1)
613,600.00 =ROUND(A1,-2)
Formula in C2 in the above example: =ROUND(A2,2)
- MROUND
- Syntax: MROUND(number, multiple)
Returns a number rounded to the desired multiple. It rounds up, away from zero, if the remainder of dividing the number by the multiple is greater than or equal to half the value of the multiple.
Formula in B1: =MROUND(A1,0.05)
- ROUNDUP
- Syntax: ROUNDUP(number, number of digits)
Rounds a number up, away from zero. If number of digits is less than 0, then the number is rounded up that many places to the left of the decimal point.
With the number 613557.188 in cell A1, the following would result from the given formula:
613,557.19 =ROUNDUP(A1,2)
613,557.20 =ROUNDUP(A1,1)
613,558.00 =ROUNDUP(A1,0)
613,560.00 =ROUNDUP(A1,-1)
613,600.00 =ROUNDUP(A1,-2)
- CEILING
- Syntax: CEILING(number, significance)
Rounds a number up to the nearest integer or to the nearest multiple of significance.
With the number 613557.188 in cell A1, the following would result from the given formula:
613,557.20 =CEILING(A1,0.05)
613,557.50 =CEILING(A1,0.5)
613,560.00 =CEILING(A1,5)
613,600.00 =CEILING(A1,50)
614,000.00 =CEILING(A1,500)
- ROUNDDOWN
- Syntax: ROUNDDOWN(number, number of digits)
Rounds a number down, toward zero. If number of digits is less than 0, then the number is rounded down that many places to the left of the decimal point. With the number 613557.188 in cell A1, the following would result from the given formula:
613,557.18 =ROUNDDOWN(A1,2)
613,557.10 =ROUNDDOWN(A1,1)
613,557.00 =ROUNDDOWN(A1,0)
613,550.00 =ROUNDDOWN(A1,-1)
613,500.00 =ROUNDDOWN(A1,-2)
- FLOOR
- Syntax: FLOOR(number, significance)
Rounds a number down, toward zero, to the nearest multiple of significance. With the number 613557.188 in cell A1, the following would result from the given formula:
613,557.15 =FLOOR(A1,0.05)
613,557.00 =FLOOR(A1,0.5)
613,555.00 =FLOOR(A1,5)
613,550.00 =FLOOR(A1,50)
613,500.00 =FLOOR(A1,500)
- INT
- Syntax: INT(Number)
Rounds a number down to the nearest integer.
- EVEN
- Syntax: EVEN(Number)
Rounds a number up to the nearest even integer.
- ODD
- Syntax: ODD(number)
Rounds a number up to the nearest odd integer.
- TRUNC
- Syntax: TRUNC(number, number of digits)
Truncates a number by removing the fractional part of the number. With the number 613557.188 in cell A1, the following would result from the given formula:
613,557.18 =TRUNC(A1,2)
613,557.10 =TRUNC(A1,1)
613,557.00 =TRUNC(A1,0)
613,550.00 =TRUNC(A1,-1)
613,500.00 =TRUNC(A1,-2)
The roundsamp.xls workbook contains these samples.
