Spreadsheet Practice

CEILING function

Quick answer

CEILING rounds a number up to the next multiple you choose: the next 5, the next dozen, the next quarter hour.

Syntax
=CEILING(number, [significance])
Most common use
=CEILING(B2,5) returns 25 (Up to the next multiple of 5: 23 becomes 25)

Use it for order quantities that come in packs, prices that end on a set step, or time billed in blocks.

Available in
Every current version
Category
Math functions
Returns
The smallest multiple of significance that is at least number. Negative numbers round up toward zero.

Syntax

=CEILING(number, [significance])

ArgumentRequired?What it is
numberRequiredThe value to round up.
significanceOptionalThe multiple to round up to, such as 5, 12 or 0.25. Excel requires it in CEILING; CEILING.MATH makes it optional.

Examples

Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.

CellFormulaResultWhat it shows
D2=CEILING(B2,5)25Up to the next multiple of 5: 23 becomes 25
D3=CEILING(B3,0.25)4.25Up to the next quarter: 4.12 hours becomes 4.25
D4=CEILING(B4,2)-2For a negative number, rounding up moves toward zero: -2.5 becomes -2

Try it live

This is the sample sheet. Change any number or text, or edit a formula, and the results update.

Show the sample data as a table
ItemAmountRounded up
Shipping weight (lb)2325
Labor hours4.124.25
Credit-2.5-2

Practice CEILING

Graded instantly, then re-checked on changed data.

Order in full packs

Parts only come in packs. In D2, use CEILING to round each quantity needed up to a whole number of packs, using the pack size in column C. Fill down to D6.

Common mistakes

What you seeWhy, and how to fix it
#NUM!number is positive but significance is negative, such as =CEILING(2.5,-2). Use a positive significance.
Excel says too few argumentsCEILING needs significance. Use =CEILING(A2,1) for whole numbers, or CEILING.MATH(A2), where it's optional.
Wanted the nearest multiple, not the next one upCEILING always goes up. Use MROUND to round to whichever multiple is closer.

Tips

  • Packs of different sizes? Point significance at a cell: =CEILING(B2,C2) rounds each quantity up to its own pack size.

Questions

How do I round up to the nearest 0.5 in Excel?

Use =CEILING(A2,0.5). 3.1 becomes 3.5 and 3.6 becomes 4.

What is the difference between CEILING and ROUNDUP?

ROUNDUP rounds to decimal places or powers of ten. CEILING rounds up to any multiple, such as 5 or 12.

What is CEILING.MATH?

A newer version (Excel 2013 and later) where significance is optional and a third argument controls how negative numbers round.

Answers that use CEILING

In other languages

CEILING is MULTIPLO.SUPERIOR in Spanish Excel and OBERGRENZE in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.

Updated Checked by running: every formula on this page was run by our formula engine on .