Functions used to round values
You can use several of the numeric functions to round a number in different ways, depending on the purpose and desired result.
Round away from zero
Use any of the functions listed below to round a number away from zero.
Function | Examples |
|---|---|
CEILING: Use to round a number away from zero to the nearest multiple of a given number. Rounding occurs in steps; for example, the closest multiple of 10. | =CEILING(12,10) returns 20, as rounding is away from zero. =CEILING(0.4,1) returns 1. =CEILING (–0.4,–1) returns –1. |
EVEN: Use to round a number away from zero to the nearest even number. Rounding is to the nearest number evenly divisible by two. | =EVEN(0.4) returns 2, as rounding is away from zero. =EVEN(–0.4) returns –2. |
ODD: Use to round a number away from zero to the nearest odd number. | =ODD(1.4) returns 3, the nearest odd number when rounding away from zero. =ODD(–1.4) returns –3. |
ROUNDUP: Use to round a number up (away from zero) to the specified number of places. If the sign of the second argument is positive, this argument indicates the number of digits (decimal places) to the right of the decimal separator to include in the rounded number. If the sign of the second argument is negative, this argument indicates the number of digits to the left of the decimal separator to replace with zeros (the number of zeros at the end of the number). | =ROUNDUP(1125.373,2) returns 1,125.38, since rounding is away from zero. =ROUNDUP(1125,–2) returns 1,200. =ROUNDUP(-1125.373,2) returns -1,125.38. =ROUNDUP(-1125,–2) returns -1,200. |
Round towards zero
Use any of the functions listed below to round a number towards zero.
Function | Examples |
|---|---|
FLOOR: Use to round a number towards zero to the nearest multiple of a given number. Rounding occurs in steps; for example, the closest multiple of 10. | =FLOOR(12,10) returns 10, as rounding is towards zero. =FLOOR(0.4,1) returns 0. =FLOOR (–0.4,–1) also returns 0. |
ROUNDDOWN: Use to round a number down (towards zero) to the specified number of places. If the sign of the second argument is positive, this argument indicates the number of digits (decimal places) to the right of the decimal separator to include in the rounded number. If the sign of the second argument is negative, this argument indicates the number of digits to the left of the decimal separator to replace with zeros (the number of zeros at the end of the number). Rounding is towards zero. | =ROUNDDOWN(1155.376,2) returns 1,155.37, since rounding is towards zero. =ROUNDDOWN(1155,–2) returns 1,100. =ROUNDDOWN(-1155.376,2) returns -1,155.37. =ROUNDDOWN(–1155, –2) returns –1,100. |
Other types of rounding
Use any of the functions listed below to round a number in other ways.
Function | Examples |
|---|---|
INT: Use to round a number to the nearest integer that is less than or equal to a given number. | =INT(0.4) returns 0, as this is the nearest integer less than or equal to 0.4. For positive numbers, rounding is towards zero. =INT(-0.4) returns -1, as this is the nearest integer less than or equal to -0.4. For negative numbers, the rounding is away from zero. |
MROUND: Use to round a number to the nearest multiple of the given number. This differs from CEILING, which rounds up to the nearest multiple. | =MROUND(4,3) returns 3, since 4 is closer to 3 than to the next multiple of 3, which is 6. =CEILING(4,3) returns 6, the nearest multiple of 3 when rounding up. |
ROUND: Use to round a number to the specified number of places. If the sign of the second argument is positive, this argument indicates the number of digits (decimal places) to the right of the decimal separator to include in the rounded number. If the sign of the second argument is negative, this argument indicates the number of digits to the left of the decimal separator to replace with zeros (the number of zeros at the end of the number). | =ROUND(1125.376,2) returns 1,125.38. =ROUND(1125,–2) returns 1,100. =ROUND(-1125.376,2) returns -1,125.38. =ROUND(-1125,-2) returns -1,100. =ROUND(-1155,-2) returns -1,200. |
TRUNC: Use to truncate a number at the specified number of places. If the sign of the second argument is positive, this argument indicates the number of digits (decimal places) to the right of the decimal separator to include in the rounded number. If the sign of the second argument is negative, this argument indicates the number of digits to the left of the decimal separator to replace with zeros (the number of zeros at the end of the number). Extra digits are stripped from the number. | =TRUNC(1125.356,2) returns 1,125.35, as the number is truncated after the two places to the right of the decimal. =TRUNC(–1125.356,2) returns –1,125.35. =TRUNC(1155,–2) returns 1,100, as the number is padded with zeros for two places to the left of the decimal. |