CEILING and FLOOR Functions
Round numbers up or down to specific multiples.
Task:
You are setting prices and want to ensure they are rounded to the nearest $5 for simplicity. Round the original prices up to the nearest $5 in column B using CEILING and down to the nearest $5 in column C using FLOOR to create consistent and appealing price points.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of CEILING, FLOOR
0/4The same formula in the other shapes it takes at work.
- CEILING and FLOOR Functions
Round numbers up or down to specific multiples.
CoreIntermediate - FLOOR to the quarter hour
The multiple can be a fraction: bill time in completed quarter hours.
Intermediate - CEILING for a .99 price ending
Round up to the next whole unit, then take a penny off.
Intermediate - CEILING to buy whole packs
You need 130 screws; they come in boxes of 50. What does that cost?
Advanced
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | |
|---|---|---|---|
| 1 | Original Price | Ceiling ($5) | Floor ($5) |
| 2 | 23.45 | ||
| 3 | 47.89 | ||
| 4 | 31.12 | ||
| 5 | 56.78 |
Functions used here
What this exercise teachesMay contain the answer
CEILING and FLOOR functions are useful for rounding numbers to specific multiples, often used in pricing, scheduling, or resource allocation.