In Google Sheets, the CEILING function allows you to round a number up to the nearest integer based on a specified factor. It can also round down if both the number and the factor are negative.
However, it’s important to note that the CEILING.MATH and CEILING.PRECISE functions are more robust alternatives to the CEILING function.
Syntax
=CEILING(value, factor)
- value: the number to be rounded
- factor: the multiple to round to
Similar Functions
Google Sheets offers several rounding-related functions. Choose the one that best suits your needs:
- CEILING.MATH: Rounds a number up to the nearest integer, with customizable options for handling negative numbers.
- CEILING.PRECISE: Rounds a number up to the nearest integer, without customizable options for handling negative numbers.
- INT: Rounds a number down to the nearest integer.
- FLOOR.MATH: Rounds a number down to the nearest integer, with customizable options for handling negative numbers.
- FLOOR.PRECISE: Rounds a number down to the nearest integer, without customizable options for handling negative numbers.
- 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: Rounds a number down to a specified number of decimal places.
- ROUNDUP: Rounds a number up to a specified number of decimal places.
- TRUNC: Truncates a number to a specified number of significant digits, omitting less significant digits.
Possible Errors
Incorrect use of the CEILING function can result in the following errors:
- Error 1
- Error 2
Examples
The CEILING function can be used in various ways. Here are a few examples to illustrate its usefulness.
Example 1 – Rounding to the nearest nickel
Sometimes, it’s preferable to present a price rounded to the nearest nickel. Here’s how you can do it:
The formula used in cell C2: =CEILING(A2,B2)
We use the CEILING function to round the original price to a multiple of $0.05. Since the function rounds up, it rounds $1.23 to $1.25 in row 2 and rounds $1.27 to $1.30 in row 4. Google Sheets doesn’t modify the value in row 3 because $1.25 is already a multiple of $0.05.
Example 2 – Rounding to the nearest half-hour
Next, let’s round some time values. It’s common to refer to a time using a rounded value. In this example, we’ll round the values to the nearest half-hour.
The formula used in cell C2: =CEILING(A2,B2)
⚠️ You can enter a half-hour using the format “0:30” or “0:30:00”. In either case, you’ll need to use a custom formatting to remove the seconds, as shown in the previous example in column B.
Both rows 2 and 4 are rounded to the next half-hour. Since 1:30 PM is already a multiple of 30 minutes, the CEILING function doesn’t modify it.
Example 3 – Rounding with different factors
So far, we’ve been changing the value. Now let’s explore changes in the factor. We’ll use both positive and negative factors to observe the differences.
You can see that positive and negative numbers approach the same absolute values. Each result in column C has the opposite sign when the inputs invert their signs.
Live Example in Sheets
Make a copy of this spreadsheet to get the examples in your own Google Sheet.
You can learn more about Google Sheets functions and how to use them to enhance your spreadsheets by visiting Crawlan.com.
