CEILING and FLOOR in Excel: how to use it
CEILING rounds up to a multiple, such as a quarter of an hour or a box of 6. FLOOR rounds down.
Try the first exercises for freeIn the lesson Rounding to a multiple
What do CEILING and FLOOR do?
CEILING rounds a number up to a multiple you choose, such as 15, 6 or 0.05. FLOOR rounds down, and MROUND rounds to the nearest multiple.
Where ROUND chooses the number of decimal places, here you choose steps: per quarter of an hour, per box of 6, per 0.05. That often comes up with prices, hours and packaging.
When do you use CEILING and FLOOR?
- Charging minutes worked per quarter of an hour started: =CEILING(A2,15).
- How many bottles you order if they come in packs of 6: =CEILING(A2,6).
- Rounding an amount to the nearest 0.05: =MROUND(A2,0.05).
- Rounding a price down to a whole number: =FLOOR(A2,1).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Up, to a multiple.
Filled in: =CEILING(A2,15)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
CEILING | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | CEILING |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
number | The number you're rounding. | A2 |
multiple | The step you round to, such as 15, 6 or 0.05. | 15 |
, | A comma separates the arguments from each other. | , |
CEILING and FLOOR step by step
C1 contains =CEILING(A2,15). 52 minutes becomes 60: every quarter of an hour you start counts. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click C1 and type CEILING with a bracket, followed by the cell with the minutes, A2, and a comma.
=CEILING(A2, - Type the multiple you're rounding to: a quarter of an hour is 15 minutes.
=CEILING(A2,15 - Close the bracket and press Enter: 52 minutes becomes 60, because the fourth quarter of an hour has started.
=CEILING(A2,15)
More examples
Down to a multiple
C1 contains =FLOOR(A2,15). Only full quarters of an hour count: 52 minutes becomes 45.
To the nearest 0.05
C1 contains =MROUND(A2,0.05). To the nearest 0.05: 2.47 becomes 2.45, and 2.48 would become 2.50.
Per box of 6
C2 contains =CEILING(A2,B2)/B2. 20 first becomes 24, and 24 bottles make 4 boxes of 6.
To the nearest multiple
MROUND rounds to the nearest multiple: =MROUND(A2,0.05) turns 2.47 into 2.45 and 2.48 into 2.50.
The difference with ROUND
ROUND chooses how many decimal places are left. CEILING and FLOOR choose a multiple, such as 5, 15 or 100.
Where it often goes wrong
- CEILING always rounds up, even if the number is closer to the lower multiple.
- When you round prices, choose deliberately between always rounding up and rounding to the nearest multiple: on a long list it makes a difference.
Practise this in the lesson Rounding to a multiple. The first exercises are free.
Frequently asked questions
How many boxes of 6 do I need for 20 bottles?
=CEILING(20,6)/6 gives 4: 20 first becomes 24, and that's 4 boxes of 6.
Read next
ROUNDMOD and INTLARGE and SMALLMEDIAN and MODESEQUENCE and RANDBETWEEN