CEILING Function

What is the Excel CEILING Function?

The ceiling function is a program in Excel that transforms a number and rounds it to the nearest multiple. The CEILING rounds the number up, away from zero. It is a helpful feature in finding the nearest number you want to round using the relevant significant multiple.

CEILING is helpful for anyone, from business owners to customers to even general Excel users, wanting to learn how to round using a specific multiple. It returns the calculated numeric value you tried to round.

The syntax for this equation is

= CEILING(number, significance)

Where

• Number = the numeric value you want to round.
• Significance = the multiple to which you want to round.

For instance, if you want to round to the nearest dime, you would insert a number first and then 0.10 for the significance in the formula to find the rounded integer. We are going to explain in greater detail how this works with some screenshots so you can better understand it.

By using the syntax above, you’ll be able to find the rounded number by choosing the nearest significant multiple that you want to round to.

Examples of Excel Ceiling Function

Here, we will display some examples of how the ceiling function works. Below in this screenshot are some examples we’ve computed into Excel, where we choose random numbers and round them up to the nearest multiple.

In the first example, in row 3, we wanted to round $4.50 up to the nearest dollar, so we use the =CEILING(number, significance) formula to calculate it by using $4.50 in B3 as the number, and 1 for the importance and it got us $5 for the answer in C3.

Then, we wanted to round $167 to the nearest hundred. So, we applied the same syntax for the first problem to this one, using $167 in B4 as the integer and 100 for the multiple, and got an answer of $200 in C4.

Next, we want to round $5.25 to the nearest nickel. Using the same methods used above, we got an answer of $5.50 in C5 as our response.

The following two examples listed are negative integers. In C6, we wanted to round to the nearest negative dime, which got us -$3.30. In C7, we used the same method above but removed the minus symbol in the formula, and it got us -$3.20.

This is because it’s a negative value. Using a negative sign in the formula with a negative number would round down away from zero rather than up. 

If you used a negative number and a positive significance, the value would be rounded closer to zero rather than further away from zero if you used a negative sign.

Finally, we wanted to round to the nearest tenth. We were using 15 as the integer got us $20 in C8.

CEILING Vs. FLOOR

Now that we’ve gotten a pretty good idea of what a CEILING does, you are probably asking: is there a similar function where you can round down instead of up to the nearest multiple? The answer is yes. It’s called the floor function.

FLOOR and CEILING functions are similar in using the same formula. The most significant difference is that the floor function rounds down to the nearest multiple, closer to zero, instead of up, away from zero like the CEILING.

For reference, the syntax for the floor is

= FLOOR(number, significance)

Where,

Number = the numeric value you want to round.

Significance = the multiple to which you want to round.

This article will walk you through some examples of how the FLOOR function works so you can better understand how it differs from CEILING. Below is a screenshot showing the examples from the last screenshot with new examples showcasing the FLOOR function.

As you can see in the first example, the CEILING function rounded $4.50 up to $5, but the FLOOR function rounded $4.50 to $4 because CEILING represents the top number, while the FLOOR represents the rounded bottom integer.

Next, we wanted to find “the floor” of $167, so we used the =FLOOR(number, significance) syntax to calculate the bottom rounded integer as we did in the last one, and it got us $100 in G4.

Then, we want to calculate $5.25 down to the nearest fifteenth of a cent. The same method used to compute the other problems got us an answer of $5 in G5.

Next, we will take two negative integers, one with a negative reference and one without. As you can see, the one with the negative reference rounded up, while the one without rounded further away from zero.

Finally, we want to round $15 down to the nearest $10 and get $10 as the answer in G8.

Researched and authored by Marcu Andrei Dumitrescu | LinkedIn

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• AND Function
• CHOOSE Function
• NOT Function
• NOW Function
• XOR Function