CEILING Function in Google Sheets: How to Use It with Examples

The CEILING function in Google Sheets allows you to round a number up to the nearest integer multiple of a specified significance. In contrast, the FLOOR function rounds a number down, while the MROUND function rounds a number to the nearest integer multiple of another number. Understanding these functions can simplify your calculations and data manipulation in Google Sheets.

Syntax

CEILING(value, [factor])

Arguments

  • value: The number you want to round up.
  • factor (optional): The multiple to which you want to round the value. If omitted, it defaults to 1. Note that the factor cannot be 0 and must be positive.

Example of the CEILING Function in Google Sheets

Let’s explore how to use the CEILING function with a few examples. Note that the formula results (Column C) will always be greater than or equal to the values in Column A.

Usage of the CEILING Function in Google Sheets

Example Formula

Using direct values instead of cell references, you can write:

=CEILING(8, 3)

Result: 9

In this formula, 8 is the value and 3 is the factor. The CEILING function rounds 8 up to the nearest integer multiple of 3.

If you were to use the FLOOR function in this case:

=FLOOR(8, 3)

Result: 6

Here, the FLOOR function rounds 8 down to the nearest multiple of 3. The multiples of 3 are:

  • 1 x 3 =3
  • 2 x 3= 6
  • 3 x 3 = 9

Since 8 is closer to 9 when rounding up, the CEILING function returns 9.

Important Notes

  1. The factor argument is optional, but if you omit it, the function treats it as 1.
  2. The factor must be positive; the value can be either positive or negative.
  3. CEILING is commonly used to round up decimal values. For example:
=CEILING(23.91, 0.05)

Result: 23.95

Practical Use Case: Currency Conversion

You can utilize the CEILING function in Google Sheets to round up currency denominations. For example, at the time of writing, the exchange rate from INR to USD is approximately 0.01189029, meaning 1 INR equals 0.01189029 USD. (I obtained this value using the GOOGLEFINANCE function, such as =GOOGLEFINANCE("CURRENCY:INRUSD").) Therefore, converting 1450.00 INR to USD gives you:

=1450 * 0.01189029

This equals approximately 17.2269205 USD. To round this amount up to the nearest 5 cents, use the CEILING function:

=CEILING(17.2269205, 0.05)

Result: 17.25 USD

This helps ensure you’re always rounding up to a more convenient currency value.

Conclusion

The CEILING function is a powerful tool for rounding numbers up to specific increments in Google Sheets. Understanding its syntax and how it compares with similar functions like FLOOR and MROUND will enhance your data manipulation skills.

Enjoy using the CEILING function in your spreadsheets!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.