CEILING vs FLOOR in Excel: Round Up or Down to a Step

The short answer to CEILING vs FLOOR in Excel: both round a number to a multiple of a step you choose, such as 5, 0.05 or 15 minutes. CEILING rounds up to the next multiple, and FLOOR rounds down to the previous one. This comparison is for anyone who prices products, orders in packs, bills time in blocks or groups numbers into bands.

CEILING vs FLOOR at a glance#

CEILING and FLOOR take the same arguments and move in opposite directions.

The second argument, significance, is the step: the multiple you want to round to. Think of it as the size of the box the number must fit into.

How CEILING and FLOOR work#

Both functions take a number and a step, and return a multiple of that step.

CEILING: round up to a step#

CEILING returns the smallest multiple of the step that is greater than or equal to the number.

=CEILING(23,5)
=CEILING(4.12,0.05)
=CEILING(B2,C2)

The first returns 25 and the second returns 4.15. In the third, if B2 holds 23 ordered units and C2 holds a pack size of 6, the result is 24: four full packs. To get the number of packs instead of units, divide: =CEILING(B2,C2)/C2 returns 4.

CEILING is the right tool whenever falling short is not allowed. You can't ship 3.8 packs or book 1.4 meeting rooms.

FLOOR: round down to a step#

FLOOR returns the largest multiple of the step that is less than or equal to the number.

=FLOOR(23,5)
=FLOOR(4.12,0.05)
=FLOOR(B2,C2)

These return 20, 4.10 and, with 23 and 6, 18: the units in three complete packs. FLOOR answers "how much fits completely?", such as full boxes you can fill, or the start of the band a value belongs to.

FLOOR also pairs well with a simple subtraction to find what is left over. =B2-FLOOR(B2,C2) returns 5 for 23 units in packs of 6: the units that don't fill a complete pack. That is the same answer as =MOD(B2,C2). In a warehouse sheet, you might show three columns side by side: full packs with FLOOR, leftover units with MOD, and packs to order with CEILING. Seeing all three makes it obvious which one a question is asking for.

Same data, both functions#

Here is a small price and time list, rounded both ways:

Every row moves in the expected direction. The time row works because Excel stores times as fractions of a day, so a step of "0:15" (15 minutes) is a valid multiple. Format the result cells as time, or they will show decimals like 0.385.

Price points ending in .99#

A common pricing rule is "round up to the next whole amount, then subtract one cent":

=CEILING(A2,1)-0.01

For 12.30 the result is 12.99. For 4.12 it is 4.99. Be careful with a price that is already whole: =CEILING(13,1)-0.01 returns 12.99, which is lower than the original price. If that matters, add a check with IF.

Grouping values into bands#

FLOOR is handy for bands. =FLOOR(B2,10) turns a score of 67 into 60, so you can count scores in the 60s with COUNTIF. For age bands, =FLOOR(B2,5) turns 34 into 30. The MOD and INT guide shows the related trick =INT(B2/10)*10, which gives the same bands for positive numbers.

How CEILING and FLOOR handle negative numbers#

With a negative number and a positive step, CEILING rounds toward zero and FLOOR rounds away from zero. So =CEILING(-23,5) returns -20, and =FLOOR(-23,5) returns -25. Both are still "up" and "down" on the number line: -20 is higher than -25.

If both the number and the step are negative, the directions flip: =CEILING(-23,-5) returns -25. A positive number with a negative step returns #NUM!.

CEILING.MATH and FLOOR.MATH, added in Excel 2013, make this simpler. Their step is optional (default 1), its sign is ignored, and an optional third argument, mode, controls which way negative numbers go. =CEILING.MATH(-23,5) returns -20, and =CEILING.MATH(-23,5,1) returns -25. If you share files with Excel 2010 or earlier, stick with CEILING and FLOOR.

When to use CEILING#

Use CEILING when the result must be at least the original value. It fits:

• Packs, cases and pallets: order enough to cover demand.
• Billing time in blocks, such as charging per started 15 minutes: =CEILING(A2,"0:15").
• Price points, such as rounding up to the next 0.05 or 0.99 ending.
• Capacity planning, such as tables of 8 for 45 guests: =CEILING(45,8)/8 returns 6 tables.
• Shipping weights, where carriers charge per started half kilo: =CEILING(A2,0.5).

Invoices often combine these with ROUND for tax; see building an invoice with formulas.

When to use FLOOR#

Use FLOOR when the result must not exceed the original value. It fits:

• Complete units only, such as full boxes you can fill from stock.
• Bands and buckets, such as score ranges or age groups.
• Rounding time down, such as clock-in times to the previous quarter hour.
• Discount tiers, where a spend of 237 qualifies for the 200 tier: =FLOOR(A2,100).
• Budgets and allowances that must not be exceeded when rounded.

Common mistakes with CEILING and FLOOR#

1. Confusing them with ROUNDUP and ROUNDDOWN. Those round to decimal places. =ROUNDUP(23,0) returns 23, not 25. The comparison of ROUND vs ROUNDUP vs ROUNDDOWN explains the difference.
2. Using a negative step with a positive number. This returns #NUM!. Keep the step positive.
3. Forgetting the time format. A rounded time shows as a decimal until you format it as time.
4. Expecting the nearest multiple. CEILING always goes up and FLOOR always goes down. For the nearest, use MROUND: =MROUND(23,5) returns 25 and =MROUND(22,5) returns 20.
5. Typing the step as text that isn't a time. "0:15" works because Excel reads it as a time; a step like "15 min" does not.
6. Rounding negative refunds without checking direction. Test one negative value before filling a column, as INT and TRUNC have the same trap; see INT vs TRUNC.

Our recommendation#

Pick by the rule you need to follow. If the result must cover the whole amount, use CEILING. If it must fit within the amount, use FLOOR. If it should simply be the nearest step, use MROUND. In Excel 2013 and later, CEILING.MATH and FLOOR.MATH are safer for negative numbers, while CEILING and FLOOR work in every version and appear in most existing files. The ROUND function guide and the overview of rounding functions cover the rest of the family.

In short: CEILING to cover, FLOOR to fit, MROUND for nearest.

Practise it#

Try the exercise on this page: round an order of 23 units up to full packs of 6. Then practise rounding in a live spreadsheet: you type each formula, and it checks your answer instantly.