CEILING.MATH Function
Returns a number that is rounded up to the nearest integer or multiple of significance
Returns a number that is rounded up to the nearest integer or multiple of significance
The CEILING.MATH Function is categorized under Math and Trigonometry functions. it will return a number that is rounded up to the nearest integer or multiple of significance. The function was introduced in MS Excel 2013.
As a financial analyst, we can use the CEILING.MATH function in setting the pricing after currency conversion, discounts, etc. When preparing financial models, it helps us round up the numbers as per the requirement.
=CEILING.MATH(number, [significance], [mode])
The CEILING.MATH function uses the following arguments:
If we omit the argument, it takes the default value of 1, that is, it will round up to the nearest integer. Significance will ignore the arithmetic sign. Remember that by default, the significance argument is +1 for positive numbers and -1 for negative numbers.
To understand the uses of the CEILING.MATH function, let’s consider a few examples:
Let’s see the results from the function when we provide the following data:
Number (argument) | Significance (argument) | Mode | Result | Remarks |
---|---|---|---|---|
210.67 | 211 | As the [significance] argument is omitted, it takes on the default value of 1. | ||
10 | 3 | 1 | 12 | The function rounds up to nearest multiple of 3. Even though mode is 1 but as the number is positive, the mode argument will not affect the result. |
32.25 | 0.1 | 32.3 | It rounded up away from zero. | |
-32.25 | -1 | 1 | -33 | It rounds -32.25 down (away from 0) to the nearest integer that is a multiple of 1 with a mode of 1, which reverses rounding direction away from zero. |
450 | 100 | 500 | It rounded up to the nearest multiple of 100. | |
$5.37 | 1 | 6 | It rounded up to the nearest multiple of 6. |
The formula used and results in MS Excel are shown in the screenshot below:
Suppose we wish to know how many containers we will need to hold a given number of items. The data given to us is shown below:
The items per container indicate the number of items that can be held in a container.
The formula we will use is =CEILING.MATH(A2,B2). It rounds up A2 to the nearest multiple of B2 (that is items per container). The value derived will then be divided by the number of containers. For example, in the second row, =CEILING.MATH(385,24)/24, 385 will be rounded to a multiple of 24 and the result will be divided by 24.
We get the result below:
Click here to download the sample Excel file
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:
To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!