The CEILING.MATH function will round any number up to the nearest integer and if desired, a specific multiple.
Contents:
Syntax
= CEILING.MATH(number, [significance], [mode])number = negative or positive number to be rounded up
[significance] = optional, multiple to which number argument will be rounded up to
[mode] = optional, changes the direction in which negative numbers are rounded
Explanation
The CEILING.MATH function is part of the "Math and Trigonometry" group of functions within Excel.
This function combines two other functions, the INT function and the MROUND function. CEILING.MATH will round any number, positive or negative, up to the nearest integer or desired multiple.
Using the optional significance argument will change the formula to round upwards to the specified multiple. A multiple being any number that can be divided by another number without a remainder left over.
For example, 7 rounded to the nearest multiple of 10, would be rounded upwards to 10. While an 11 rounded to the nearest multiple of 5, would be rounded upwards to 5.
If the number is already an exact multiple, no rounding will be performed.
The mode argument can only be used when rounding negative numbers and changes the direction of the round. The default behavior is to round towards zero.
There are two inputs that can be used for the Mode argument.
Mode = 0 or "FALSE" - (default behavior) rounds up towards zero
Mode = 1 or "TRUE" - rounds down away from zero
Notes:
- If any input is non-numerical, a #VALUE! error will be returned
- The number and the multiple must have the same sign
- A blank cell will return a 0
Different Methods of Rounding in Excel
There are a lot of different ways to round numbers, and it all depends on the end goal you're trying to reach. Here are a few different rounding functions and when you would want to use them:
ROUND - normal rounding, rounds a specified place
MROUND - rounds to the nearest specified multiple
ROUNDUP - rounds up away from 0, to the nearest specified place
CEILING.MATH - rounds up to the nearest integer or significant multiple
ROUNDDOWN -rounds down away from 0, to the nearest specified place
FLOOR.MATH - rounds down to the nearest integer or significant multiple
INT - rounds down to the nearest integer, returns an integer
TRUNC - truncates all decimal places to specified place
Examples
1. How to Round a Number Up to The Nearest Integer
To round a number to the nearest integer, we can use the CEILING.MATH function with any input number (either a cell reference or hard coded value).
Simply drop in any number into the number argument position of the formula and the formula will automatically round any number with a decimal or fractional portion to the nearest integer.
= CEILING.MATH(number)
2. How to Round a Number Up to The Nearest Integer and Specific Multiple
The CEILING.MATH function can also be used to round numbers up to the nearest integer and specific multiple.
By adding any number to the Significance argument, the formula will round any input number up to the nearest specified multiple or number of significance.
= CEILING.MATH(number, multiple)
3. How to Round Negative Numbers to Nearest Integer, Towards or Away from Zero
The CEILING.MATH function also has the built-in ability to change whether negative numbers are rounded away from zero or towards zero.
This gives more control over the exact rounding behavior, allowing better precision over most other rounding functions.
In the first two examples, 0 is being used in the Mode argument spot to round the input numbers towards zero. In the second two examples, 1 is being used to round away from zero.
Formula to round towards zero:
= CEILING.MATH(number,,0)Formula to round away from zero:
= CEILING.MATH(number,,1)
4. How to Find The Number of Containers Needed to Hold a Given Number of Items
As a real world example, let's assume we have a few different products of different sizes we need to ship out.
If we know the size of the items and how many can fit per container, the MATH.CEILING function can be used to calculate this easily.
We are using MATH.CEILING to round the number of items up to the maximum number of items we can fit per container. This is because we can only "fit" a maximum of this multiple per container.
Any remainder, even one item, must be shipped in its own container, so it is rounded up.
We then divide the rounded number by the number of items per container to calculate the number of containers needed.
= CEILING.MATH(number_of_items, items_per_container) / items_per_container