CEILING Function – Google Sheets

Adam Steinfurth

The CEILING function in Google Sheets rounds a number up to the nearest integer multiple of a specified significance.

If the value is negative, and the factor is negative, this function rounds the value down, away from zero.

The CEILING.MATH and CEILING.PRECISE are more robust replacements for the CEILING function.

Syntax

=CEILING(value, factor)

value – The number for the function to round

factor – The multiple to round up to

Similar Functions

Several functions deal with rounding. Choose the most appropriate for your use.

  • CEILING.MATH – Rounds a number up to the nearest integer multiple of specified significance with custom negative number treatment
  • CEILING.PRECISE – Rounds a number up to the nearest integer multiple of specified significance
  • INT – Rounds a number down to the nearest integer
  • FLOOR.MATH – Rounds a number down to the nearest integer multiple of specified significance with custom negative number treatment
  • FLOOR.PRECISE – Rounds a number down to the nearest integer multiple of specified significance
  • MROUND – Rounds a number to the nearest multiple of another number
  • ROUND – Rounds a number to a specified number of decimal places using standard rounding
  • ROUNDDOWN – Round a number down to a specified number of places
  • ROUNDUP – Round a number up to a specified number of places
  • TRUNC: Truncates a number to a certain number of significant digits by omitting less significant digits

Possible Errors

#NUM! – The value and the factor are different signs. Make both positive or negative.

#VALUE! – An argument is non-numeric.

Insert Math Symbols (Add-On)

Web banner showing math symbols from the Insert Special Characters Add-On
Insert Any Math Symbol with One Click

Examples

You can use CEILING in many different ways. Let’s take a look at a few, starting with rounding currency.

Example 1 – Round Up to the Next Nickel

Sometimes presenting a price rounded up to the next nickel looks better. Let’s look at how to do that.

Using the CEILING function to round prices up
Rounding Up to the Next Nickel

The formula used in cell C2: =CEILING(A2,B2)

We use the CEILING function to round the Original Price to a factor of $0.05. Since the function rounds up, it rounds $1.23 up to $1.25 in row 2 and rounds $1.27 up to $1.30 in row 4. Google Sheets does not round the value in row 3 because $1.25 is already a multiple of $0.05.

Example 2 – Round Up to the Nearest Half-Hour

Next, let’s round some time values. It is common to refer to a time as a rounded value. In this example, we’ll round values up to the next half-hour.

The CEILING function rounding times up
Rounding Up to the Next Half Hour

The formula used in cell C2: =CEILING(A2,B2)

⚠️ You can enter a half hour as “0:30” or “0:30:00“. Either way, you’ll need to use custom formatting to remove the seconds, like in the example above in column B.

Rows 2 and 4 both get rounded up to the next half hour. As 1:30 PM is already a multiple of 30 minutes, the CEILING function does not change it.

Example 3 – Round With Different Factors

Up to this point, we have been changing the value. Now let’s look at changing the factor. We’ll use positive and negative factors to see the differences.

The CEILING function working with positive and negative numbers
Rounding Using Different Factors

You can see that negatives and positives move towards the same absolute values. Each output in column C is the opposite sign when the inputs flip their signs.

Live Example in Sheets

Make a copy of this spreadsheet to get the examples in your Google Sheet.