Excel CEILING.MATH function
The CEILING.MATH function is a new function introduced in the Excel 2013 and the later versions, now let us see what does this function offer to us.
What is the Excel CEILING – MATH function?
Excel 2013 and later versions have a built-in function called the CEILING.MATH function which is used to round a number to its nearest multiple of significance. In addition, it also provides additional operation of rounding off the negative numbers where this function can reverse the direction of negative numbers by specifying the mode parameter or say by giving a specific value to the mode parameter.
=CEILING.MATH (number, [significance] , [mode])
number (required)- This parameter represents the number to be rounded off.
[significance] (optional)- Represents the multiple to be used when rounding. The default value is equal to ‘1’.
[mode] (optional)- Used when rounding a negative number towards or away from zero. The default value is ‘0’.
How is the function used in Excel?
- The Excel CEILING.MATH function is used to round off a number to a given multiple of significance or to the nearest possible integer or. By default, the significance parameter is ‘+1’ for positive numbers and in this case, the positive decimal number is rounded off to the nearest integer. (Ex. 4.2 is rounded towards 5).
- The significance parameter for negative numbers is ‘-1’ and in this case, the negative decimal number is rounded up(towards 0) to the nearest integer (Ex. -8.7 is rounded to a -8).
Rounding negative numbers
The rounding of negative numbers towards zero or away from zero is provided via the (optional) mode parameter where the default value of the mode is ‘0’.
- If the mode parameter is omitted, or when this is made a ‘0’ then the CEILING.MATH function rounds off the negative decimal number towards zero (Ex -3.2 is rounded to a -3).
- When the mode is made a non-zero value, then the CEILING.MATH function rounds the negative decimal number away from zero (Ex.-3.2 is rounded to a -4). Note that the mode has no effect on the positive decimal numbers.
CEILING.MATH versus the CEILING function
- When the sign of a number and when the significance arguments are different, then the Excel CEILING function returns a ‘ #NUM!’ Error. This error can be totally avoided when the Excel CEILING.MATH function is used.
- The Excel CEILING.MATH function provides an excellent explicit control for rounding off the negative numbers either towards zero or away from zero.
Let us now understand the rounding of the decimal numbers using the Excel CEILING.MATH function and use the generic formula to formulate the result to round off values in different cases and these are as shown below. The result obtained thus depends on the significance and the parameters.
Figure 1. The Excel CEILING.MATH function used for rounding off of numeric values in different cases.
- The number is not rounded if it is the exact multiple of the significance parameter.
- Also note that if you want to round off to the nearest multiple be it up or down, then the MROUND function can also be used.