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.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
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 teachesMay contain 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.