The CEILING function is an important Google Sheet function that helps you round up a specific number to the number’s nearest given integer multiple.
However, the CEILINGfunction is not similar to ROUND, INT, TRUNCT. But it works similarly with the FLOOR function with one major difference. Google Sheets’ CEILINGfunction rounds up the number while the FLOOR function rounds down a number.
There are several techniques for rounding numbers in Google Sheets, and we will look at five of them in this article.
But first, let us learn the basic rules of the CEILINGfunction in Google Sheets. We need to consider several rules when working with the CEILINGfunction, but here are the three most important rules that you should know by heart.
3 Important Rules of the CEILINGFunction
- The function has only two arguments.
- It does not work with non-numeric characters.
- There are two ways to write the function, first, by substituting the first and second argument by their reference cells, and second by substituting the formula by the actual value.
Are you ready to know more about the CEILINGfunction?
The Anatomy of the CEILING Function
Here’s the syntax (the way we write) of the function:
Let’s go ahead and dissect its parts and their usage.
- = the equal sign starts the
CEILINGfunction. This will notify your Google Spreadsheet that you will write a formula. - CEILING() is our
CEILINGfunction. It rounds up a specific number considering the number or value’s factors. - Value refers to the argument that we will round up to its nearest whole number multiple of a specific factor.
- Factor is the CEILING function’s the second argument. It is a conditional integer where its multiples can be rounded.
Points to Ponder:
Value – a positive value must be accompanied by a positive factor. However, if the value is negative, the number falling under the “factor” argument can be positive or negative.
Argument – There are only two arguments in a CEILINGfunction, the value, and the factor. When writing the syntax, you can either substitute the value by its reference cell or the integer itself. The same goes for the second argument. Thus, you can also write the syntax like this,
The downside with adding the value manually is you can not copy-paste the formula to the following cells as it will paste the value of the source cell.
You might be wondering when and how to use this function. Or, you may be wondering if this applies to a real-life scenario. Certainly! This function is helpful for professionals and students working or dealing with numbers.
Here’s a real-to-life example where we can apply the CEILINGfunction.
A Real Example of Using CEILING Function
A merchandiser wishes to gather all their products’ ratings for the monthly evaluation. She gathered the product’s name and product ratings (good and bad). After that, she was then asked to compute and round up each product’s total rating with a condition. The management asked her to round up the numbers to the nearest multiple numbers, which is the bad product ratings.
Listed below are the ratings that the merchandiser gathered.
| Product | Product Rating – Good | Product Rating – Bad |
| Coffee | 1,073 | 250 |
| Makeup | 1500 | 175 |
| Alcohol | 2000 | 475 |
While computing, the merchandiser uses the CEILINGfunction’s formula where the good rating will substitute the first argument, which is the value. On the other hand, the bad rating will substitute as a conditional factor.
Here’s what the merchandiser pulled up.
| Product | Result |
| Coffee | 1250 |
| Make-up | 1575 |
| Alcohol | 2375 |
The merchandiser generated the report using the CEILINGformula:
Before we proceed, click and open the example spreadsheet below. We will use this as we go along.
How to Use CEILING Function in Google Sheets
- Firstly, input all the given data in a specific spreadsheet.
- Secondly, set designated cells for the results. You can put the resulting value on the next column or row. We set it right beside the Factor cells in our example, with cells ranging from D2 to D4.
3. Now that you have everything ready, we will start writing the formula. Remember, every Google Function’s syntax starts with an “=” sign.
In some cases, the spreadsheet will automatically detect the cells and suggest the operation or function be done. To cancel the suggested function, you have to click the “x” icon when it pops up. In this example, we enclosed it in a yellow rectangle.
Also, you can press the “Backspace” key on your keyboard to remove the suggested operation.
4. Next, we will now add the CEILINGfunction which is CEILING().
Note: Only add the close parenthesis after adding the factor. You can directly key it in the cell or the function bar when adding the function.
5. After adding the function, we will add the first argument, which is the ‘value.’ After writing the value, we will add a comma, ” as a separator for the next argument.
6. Let us now add the second argument, which is the factor. Then, we will add a closing parenthesis after the factor’s value to complete the CEILINGfunction.
7. Lastly, to generate the result or the Returning value, press the “Enter” key.
Frequently Asked Questions
What is the function of the CEILING formula in the Google Sheets spreadsheet?
Google Sheets’CEILING function in Excel rounds a value up to a specified multiple. The significance argument includes the multiple to utilize for rounding. If the value is already an exact multiple, there is no rounding, returning the original value.
There are only two arguments in a CEILING function: a value and a factor.
Are theCEILING function and floor function the same?
The CEILING function produces the least near integer for a given number, and the FLOOR function provides the greatest nearest integer.
What is the CEILING value of Google Sheets?
The nearest number bigger than or equal to a specified number is returned. The ceiling function is frequently used as a rounding function. This is a function with a single value.
In conclusion, writing the CEILING function in Google Sheets is easy. However, you must be cautious about adding the data in each cell. This is because the CEILING function is value-sensitive, just like the other functions in Google Sheets.
You may also check other Google Sheets functions to help you round numbers. Visit our website at Sheetaki for more Google Sheets functions articles.
Don’t forget to hit the “Newsletter” tab and click on the “Subscribe” icon located at the upper right-hand side of the screen to get more updates about Google Sheets spreadsheet tutorials like this.
