Ceiling.Math Function in Google Sheets

What is CEILING.MATH function in Google Sheets?

The CEILING.MATH function rounds up a number to the nearest integer or the nearest multiple of specified significance. The function also specifies whether the number has been rounded toward or away from 0 based on the mode. It is helpful in various scenarios, especially when dealing with financial data. For instance, when you need to calculate interest on an investment or loan, you must round up the amount up to the nearest dollar. Let us look at a simple example below. Given below are a few sample numbers on which we apply the formula.

Here, the function can hold three arguments, but the second and third are optional. In the first scenario, the number is rounded to the nearest integer. The second number is rounded off to the nearest integer, which is a multiple of 4. In the third scenario, the number is rounded off to the nearest multiple of 5, towards zero, as we have specified zero as the third argument.

Ceiling.Math() Google Sheets Formula

Now that we have looked at some simple examples let us read further to understand how the function works. To do so, you should know the syntax of the function.

CEILING.MATH(number, [significance], [mode])

• number (mandatory) – It represents the value to round up to the nearest integer or the nearest multiple of significance.  
• significance (optional) – The value to whose nearest multiple the number will be rounded. It is one by default.
• mode (optional) – For negative numbers, if 0 or blank, it’s rounded up towards zero.

Otherwise, it’s rounded down away from zero.

How to Use Ceiling.Math Function in Google Sheets?

As seen before, the Ceiling.Math function in Google Sheets is used to round a given number up to the nearest integer and, if specified, to the nearest multiple of significance. Let us look at how we can use it with a simple example. We are given a number that should be rounded to the nearest multiple of significance.

Choose the cell where you want to enter the formula for CEILING.MATH.

Step 1: To find the nearest integer or the nearest multiple of significance, we will apply the CEILING.MATH.  Go to cell B2, as shown below, and type,

=CEILING.MATH(

Step 2: Manually enter the parameters according to the syntax, as shown below.

= CEILING.MATH(number, [significance], [mode])

In cell B2, we enter the values =CEILING.MATH(-100,3,1) as shown below.

Step 3: Press Enter. You can see the result displayed in cell B2.

Since we are specifying ‘1’ as the third argument, the result is rounded away from 0. Also, the nearest multiple of three to the specified number away from zero is -102. Hence, we get this result.

Using the Google Menubar

1. Choose the cell where you want to enter the formula for CEILING.MATH.
2. Next, proceed to the menu bar and click on “Insert” ➝ “Math” ➝ “Ceiling.Math.”
3. Enter the arguments. Here, the first argument is mandatory, which is the number to be rounded; the other two are optional. Close the bracket and press the “Enter” key.

Examples

Let us proceed further with some interesting examples of the Ceiling Math function in Google Sheets and how to use it in our everyday lives.

Example #1 – Round Interest Rates Based on Specific Rounding Rules

Given below are some interest rates levied by different banks on a loan amount. Let us round the interest rates up and see how the interest amount varies accordingly.

Step 1: We are required to round it off to the nearest 100^th value away from zero. Let us enter the following formula in cell C1.

Here,  to round the number to the nearest 1/100^th (0.01) value, we use the second argument as 1/100. Here, the third argument 1 is insignificant as there are no negative numbers.

Step 2: Press Enter. You can see the interest rounded off to the nearest 1/100^th multiple.

Step 3: Use Autofill and drag the formula to the rest of the cells till C5. You can observe the result below.

Here, the nearest hundredth (0.01) that is greater than 8.9999 is 9. Hence, we get the result 9 in cell C3.

Example #2 – Round Values to a Specific Precision for Statistical Analysis

Students enrol for different special activities provided by a university . The teachers must decide how many classrooms they may need for the various activities based on the enrolment. Let us look at how this statistical analysis is done using CEILING.MATH.

Step 1: Enter the data in the Google sheets as follows. Column A has the names of the activities, Column B, the number of students enrolled, and Column C has the number of students who can be fitted into each class.

Step 2: Enter the following formula in cell D2.

=CEILING.MATH(B2,C2).

It rounds up the number of students (A2) to the nearest multiple of the number of students allowed per class (B2)

The result divided by the number of students per class, showed how many classrooms are needed to accommodate the students. So, the final formula in cell D2 will be

=CEILING.MATH(B2,C2)/C2

Step 3: Press Enter. You get the number of classrooms needed to accommodate the students who chose “Dancing.”

Step 4: Use the formula for the other activities by dragging it up to cell D6.

Example #3 – Round Time Estimates to the Nearest Suitable Unit

At times, you may need to round values to the nearest precision unit required. You can use the CEILING.MATH function in Google Sheets for this purpose.

Look at the list of times provided in the Google Sheet in the image below.

Note: All the columns should be formatted as Time to get values you can understand.

To round these to the nearest time precision, you can use the following options.

Step 1: Let us round these times to the nearest hour. Time values in Google sheets are represented as fractions of a day. Therefore, 1 hour is 1/24th of a day. Hence, to round these time values to the nearest hour, use the following formula in cell B2.

=CEILING.MATH(A2,1/24).

Press Enter.

Step 2: You can see how it has been rounded of to the nearest hour. Now, drag the formula to get the rounded values for the other times specified.

Step 3: Similarly, to get the rounded values up to the nearest minute, use 1/1440 (24*60). Apply the following formula to cell C2.

=CEILING.MATH(A2,1/1440).

Press Enter.

Step 4: Now, drag the formula up to cell D7 and check how all the time values have been rounded to the nearest minute.

Thus, modifying the formulas according to requirements will help you round the time value using CEILING.MATH function based on different rounding precisions.

Important Things to Note

• The CEILING.MATH function can be helpful in scenarios where you need to round values, like in financial documents, by varying the mode parameter based on the nature of the transaction.
• By default, the positive numbers with any number of decimal places are rounded up to the nearest integer. E.g.,2.4 is rounded up to 3. In the meantime, negative numbers are rounded up to zero to the nearest integer. For instance, -2.8 is rounded up to -2.
• We can use the FLOOR.MATH function to round down to the nearest integer or significant figure.
• You get the #VALUE error when you use non-numeric arguments.

Frequently Asked Questions (FAQs)

Recommended Articles

Guide to What is CEILING.MATH Function in Google Sheets. Here we discuss Ceiling.Math Function in Google Sheets with its examples. You can learn more from the following articles. –

BINOM.DIST Google Sheets

Filter in Google Sheets

EXPON.DIST in Google Sheets