CEILING and FLOOR in Excel
Last updated
CEILING rounds a number up to the next multiple of the step you choose, and FLOOR rounds it down. For example, =CEILING(B2,12) turns 130 bottles into 132, a whole number of cases of 12, and =FLOOR(B2,5) puts an age of 37 in the 35 age band.
CEILING and FLOOR syntax
=CEILING(number, significance)
- number
- The value to round: a cell such as
B2, a number, or a calculation. - significance
- The multiple to round to:
12for cases of 12,15for quarter hours,0.05for 5 cents. It can come from a cell. FLOOR takes the same arguments.
Practice CEILING and FLOOR here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Round up to whole cases
The company picnic needs 130 bottles of water (B2), and they only come in cases of 12. In C2, return how many bottles to order with CEILING.
| Event | Bottles needed | Bottles to order |
| Company picnic | 130 |
How to use CEILING and FLOOR, step by step
- Click the cell for the result and type
=CEILING(to round up or=FLOOR(to round down. - Select the cell to round, or type the calculation, then type a comma.
- Type the significance, the step you want to land on, such as 12, 5, 15 or 0.05, or click the cell that holds it.
- Close the parenthesis and press Enter. A value that is already a multiple of the step stays as it is.
CEILING and FLOOR examples
Round up to whole cases
=CEILING(B2,12)
Bottles come in cases of 12: 130 becomes 132, or 11 cases.
Round down to a 5-year band
=FLOOR(B2,5)
Groups ages by band: 37 becomes 35 and 42 becomes 40.
Round a price up to 5 cents
=CEILING(B2,0.05)
Turns 3.42 into 3.45 and 9.97 into 10.00. 5.05 is already a multiple and stays 5.05.
Bill in 15-minute blocks
=CEILING(B2,15)/60*$F$1
Rounds 47 minutes up to 60, then bills one hour at the rate in F1: 80.
Common CEILING and FLOOR problems and how to fix them
| Result | Cause | Fix |
|---|---|---|
| Bottles instead of cases | CEILING returns a multiple of the significance: =CEILING(130,12) gives 132 bottles, not 11 cases. | Divide the result by the case size, or count the cases directly with =ROUNDUP(B2/12,0). |
| #DIV/0! | FLOOR got a significance of 0, often because the cell it points to is empty. | Fill in the significance cell, or type the step straight into the formula. |
| #VALUE! | The number or the significance is text, for example “15 min” typed in the cell. | Keep only the number in the cell and put the unit in the column header. |
| #NAME? | The function name is misspelled (=CEILLING) or written in another Excel language. | Type =CEI and pick CEILING from the list Excel suggests. |
CEILING and FLOOR vs ROUNDUP, ROUNDDOWN and MROUND
ROUNDUP and ROUNDDOWN round to a number of decimals, while CEILING and FLOOR round to a multiple of any step, such as 12, 15 or 0.05. To go to the nearest multiple, up or down, use MROUND: =MROUND(B2,5). Excel 2013 and later also have CEILING.MATH and FLOOR.MATH, where the significance is optional and defaults to 1. With a positive number and a positive significance they give the same results as CEILING and FLOOR; they only differ when a negative value is involved.
Frequently asked questions
How do I round up to the nearest 5 in Excel?
Use CEILING with 5 as the significance: =CEILING(B2,5) turns 37 into 40. =FLOOR(B2,5) rounds it down to 35.
How do I round a time to the next 15 minutes?
If the duration is in minutes, use =CEILING(B2,15): 47 becomes 60. For a time of day such as 8:47, give the step as a time: =CEILING(B2,"0:15") returns 9:00.
What is the difference between CEILING and ROUNDUP?
ROUNDUP rounds to a number of decimals: =ROUNDUP(3.42,1) returns 3.5. CEILING rounds to a multiple: =CEILING(3.42,0.05) returns 3.45.
Does CEILING change a value that is already a multiple?
No. =CEILING(48,12) returns 48, and FLOOR leaves it unchanged too.
Keep practising
All 5 CEILING and FLOOR exercises, with explanations and progress tracking.
Open the CEILING and FLOOR practice set