In this post, you will learn about the Ceiling.math function in Excel. This function always rounds up the number to the next higher value, and so the name Ceiling.
Syntax
=CEILING.MATH(number, significance, mode)
Arguments
The syntax has three arguments
- number – The number you want to round up
- significance – The multiple to which you want to round the number, default value 1.
- mode – For rounding negative numbers towards or away form zero, default value 0.
- The arguments in the syntax can be integer or decimal numbers.
- Both the significance and mode values are optional as they have a default value.
- Note that the number is rounded up according to the multiples and not the number itself Example =CEILING(5, 3) answer is 9 a multiple of 3.
- This function is especially used to have better control in handling negative numbers.
How to use Ceiling.math function in Excel?
- This function will round up the number towards the nearest multiple of the given significance. By default the significance value is 1 for positive numbers and -1 for negative numbers.
- Note: In negative numbers the number closer to zero is greater
- With a positive number the function rounds up the number to the nearest integer, and for a negative number it rounds up the number towards zero.
The example below shows the Ceiling.math function with the default argument values.
Sometimes when the significance value is 1, the function returns the number unchanged as the answer. This happens when there is no need to round up the number as it satisfies the significance value.
The example below shows the function with the significance value as 3.
- When working with negative numbers we can control the direction of rounding up the number, towards zero or away from zero using the mode argument.
- The mode argument has a default value 0, which rounds up the number towards zero.
- To change the direction you can replace 0 with any number or with the character ‘true’.
The following example shows the Ceiling.math function with the default argument values.
This example shows the function with the default mode argument value, this performs the same function and rounds it up towards zero.
The following example use 1 and ‘true’ as the mode argument values, this rounds up the number away from zero.
