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.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

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.