Work out how many cartons an order needs, and what's left in the last one
CEILING rounds a box count up to the next whole carton; MOD catches the partial box that plain division alone can't tell you about.
Task:
You handle order fulfillment at Thistle & Co, a home-and-gift wholesaler that ships every SKU in cartons of a fixed unit capacity. This week's orders are in front of you, each with its order quantity and how many units fit in one carton. Work out how many cartons each order needs in column D, and how many units land in the last, possibly-partial carton in column E.
Learning Objectives:
- Use CEILING to round a quantity up to the next whole unit rather than the nearest one
- Pair MOD with IF to handle the zero-remainder edge case correctly
- Recognize when ROUND or plain division silently produces the wrong count for a real quantity
Solve without hints for +5 XP
Interactive Spreadsheet
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 | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Order Qty | Units per Box | Boxes Needed | Units in Last Box |
| 2 | Ceramic mugs | 180 | 12 | ||
| 3 | Woven placemats | 250 | 24 | ||
| 4 | Water bottles | 96 | 8 | ||
| 5 | Tote bags | 137 | 20 | ||
| 6 | Scented candles | 45 | 6 |
What this exercise teaches (contains the answer)
CEILING(B2/C2,1) rounds the division up to the next whole carton, because a quantity that divides evenly still needs the same number of cartons as one that leaves a single unit over — 250 units at 24 per carton and, say, 241 units at 24 per carton both round up to 11 cartons, which plain division or ROUND would get wrong. MOD(B2,C2) reports what is left after filling as many full cartons as it can, but for Ceramic mugs and Water bottles — where the order divides evenly — that remainder comes back as 0 even though their last carton is not empty, it is simply full. IF catches that case and reports the carton's own capacity instead of a last carton with nothing in it.