- Basic Rounding with ROUND()
- Rounding in a Specific Direction with ROUNDUP() and ROUNDDOWN()
- More Control Over Rounding with FLOOR() and CEILING()
- Getting Rid of Decimals with INT() and TRUNC()
More Control Over Rounding with FLOOR() and CEILING()
ROUNDUP() and ROUNDDOWN() let you control the direction of rounding, but not the multiple. What do I mean by multiple? An example will help explain. Suppose you’re working with financial figures and don’t need the precise value, but rather the value to the nearest $100. In this case, 100 is the multiple.
You can use the FLOOR() and CEILING() functions for this purpose; FLOOR() rounds down and CEILING() rounds up. Each function takes two arguments: the value and the multiple. Here are some examples:
- =CEILING(1249, 125) evaluates to 1250.
- =FLOOR(1249, 125) evaluates to 1125.
- =CEILING(1249, 250) evaluates to 1250.
- =FLOOR(1249, 250) evaluates to 1000.
Strangely, you cannot use a negative value with a positive multiple. For example, =CEILING(-1249, 125) returns the #NUM! error. You must specify a negative multiple in cases like this. With negative values, these two functions are similar to ROUNDUP() and ROUNDDOWN in that CEILING rounds away from zero and FLOOR() rounds toward zero.