The CEILING function in Google sheet 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.
- 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.
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 for the case where the argument number is positive and multiple is negative(see row# 5 above). That will result error. In Excel if the number is negative and the multiple is positive then also it throws error but in Google sheet that is being handled(see row# 4 above).
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.
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.