December 3, 2020

CEILING function in Excel

The CEILING function in Excel rounds up any specific number to the nearest multiple of any given number. It works similar to the MROUND function but the difference between these two is MROUND can round up and down to the nearest multiple but CEILING always rounds up to the nearest multiple.

=CEILING(number, multiple)
  • number : This will be rounded up by CEILING function
  • multiple: The number will be rounded up to the nearest multiple of this mentioned significance

Let’s understand in detail with a simple example as follows. Here we have the below table, In column A there are numbers which will be rounded up to the nearest multiple of the corresponding number in column B. In column C we have used the CEILING function. In cell C1 the function we added as follows and dragged the same for the following cells in column C hence automatically updated the cell references of column A & B.

=CEILING(A1,B1)

As you can see below each cell in column C calculates the respective number in column A rounded up to the nearest multiple of column B by CEILING function.

We have used both positive and negative numbers and both decimal and integer numbers in all possible combinations to show every combination works with CEILING function except one positive and one negative argument. That will result error.

In the above example we can also see in row# 3, the number in cell A3 is already an exact multiple of cell B3, means 15 is the exact multiple of 3, hence in cell C3 no rounding happened.

In our next example we will learn how we can use the CEILING function to round up a time. In the below example in column B we have rounded up the times mentioned in cell A1 and A2 to the nearest multiple of 30 minutes.

Below is the formula we used in cell B1 and dragged the same for cell B2 to update the cell reference to A2.

=CEILING(A1,”0:30”)

With this formula, the time 7:29 has been rounded up to 7:30 which is the nearest multiple of 30 minutes. Same way in the next row, the time 12:06 has been rounded up to 12:30.

You can also check here to know how the CEILING function works in Google Sheet.

You can also check here to know how the FLOOR function works in Excel.

You can also check here to know how the FLOOR function works in Google Sheet.

Leave a Reply

Your email address will not be published. Required fields are marked *