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*