How to Use CEILING Function in Excel (With 3 Examples)

Here’s an overview of various results of the ceiling function.

Introduction to the CEILING Function

• Function Objective:

The CEILING function is used to round up a number to its nearest upper integer or to the multiple of significance.

• Syntax:

CEILING(number, significance)

• Arguments Explanation:

• Return Parameter:

Rounded up number according to the significance.

How to Use the CEILING Function in Excel: 3 Examples

Steps:

• Select cell D5.
• Insert the formula:

• Press the Enter button.
• Drag the Fill Handle icon to the end of the Output column.

Example 1 – Round up Fractions Using the CEILING Function

The first number is 6.4356 and its corresponding significance is 0.01. As the significance has two digits after the point, the rounded-up numbers will also have two digits after the decimal point. As a result, we can see the output of the ceiling function has become 6.44 which is the immediate round-up value after 6.43.

For the second number i.e. 5.7, the significance is 1, which means that there will be no digit after the decimal point. As a result, that 5.7 has become 6 after applying the formula.

Example 2 – Round up Numbers to their Multiples Using the CEILING Function

This time, the difference is that the second value (the multiple) is larger than 1. Let’s take a number such as 6. If the multiple is 5, the number will be rounded to the next multiple of 5 (which is 10).

Example 3 – Round up Negative Numbers Using the CEILING Function

We can round up negative numbers in one of the two following ways. We can round them up either towards zero or away from zero. If the sign of the significance is positive then they will be rounded up towards zero; otherwise, they will be rounded up away from zero.

Let’s pick up the second number i.e. -5.7 for illustration. Its significance specified is -1. As the sign of the significance argument is negative, the output will go away from zero. So the output as we can see in the above picture is -6.

For the last number of the list, which is -4.8, the specified significance is 1. So, the output will go towards zero to round up itself using the formula, which is -4.

Things to Remember

• If any of your inserted arguments are nonnumerical, then Excel will return the #VALUE error.
• By default, the CEILING function rounds up numbers by adjusting them away from zero (0).

Download the Practice Workbook

<< Go Back to Excel Functions | Learn Excel