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 0
Use any of the functions listed below to round a number away from 0.
Function | Examples |
|---|---|
CEILING: Use to round a number away from 0 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 0. =CEILING(0.4,1) returns 1. =CEILING(-0.4,-1) returns -1. |
EVEN: Use to round a number away from 0 to the nearest even number. Rounding is to the nearest number evenly divisible by 2. | =EVEN(0.4) returns 2, as rounding is away from 0. =EVEN(-0,4) returns -2. |
ODD: Use to round a number away from 0 to the nearest odd number. | =ODD(1.4) returns 3, the nearest odd number when rounding away from 0. =ODD(-1,4) returns -3. |
ROUNDUP: Use to round a number up (away from 0) 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 0. =ROUNDUP(1125,–2) returns 1,200. =ROUNDUP(-1125.373,2) returns -1,125.38. =ROUNDUP(-1125,–2) returns -1,200. |
Round towards 0
Use any of the functions listed below to round a number towards 0.
Function | Examples |
|---|---|
FLOOR: Use to round a number towards 0 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 0. =FLOOR(0.4,1) returns 0. =FLOOR (–0.4,–1) also returns 0. |
ROUNDDOWN: Use to round a number down (towards 0) 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 0. | =ROUNDDOWN(1155.376,2) returns 1,155.37, since rounding is towards 0. =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 0. =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 0. |
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. |