A supplier sells in full cases, so the shortfall has to be rounded up, never to the nearest case. And when there is no shortfall at all, the subtraction goes negative and ROUNDUP pushes it further below zero, so MAX has to clamp it to 0 before the rounding happens.
You run the stockroom for a small bakery, and on Friday you place the order for next week's production. The sheet in A2:D7 lists how many kilograms of each ingredient next week's bake needs, how much is already on the shelf, and the size of the case the supplier sells it in. In E2, write a formula that returns how many whole cases to order to cover the shortfall, and 0 when the shelf already holds enough. Copy it down to E7.
Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.
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 | Ingredient | Needed (kg) | On Hand (kg) | Case Size (kg) | Cases to Order |
| 2 | Bread flour | 420 | 95 | 25 | |
| 3 | Unsalted butter | 64 | 18 | 10 | |
| 4 | Caster sugar | 85 | 110 | 20 | |
| 5 | Dried yeast | 6 | 1.5 | 2 | |
| 6 | Dark chocolate | 38 | 12.5 | 5 | |
| 7 | Rolled oats | 30 | 30 | 10 |
Cases come whole, so the job is to cover the shortfall, not to land near it. The butter is short 46 kg, which is 4.6 cases of 10; ROUND would order 5 and happen to be right, but the chocolate is short 25.5 kg, 5.1 cases, and ROUND would order 5 and leave the bakery half a kilo short on Thursday. ROUNDUP with 0 digits moves any fraction, however small, up to the next whole case, which is the rule a stockroom actually follows. The flour is the check that it does not overshoot: 325 kg is exactly 13 cases, and ROUNDUP leaves a whole number alone. The caster sugar is where the formula earns the MAX. There are 110 kg on the shelf against 85 needed, so the shortfall is -25 and the division gives -1.25. ROUNDUP rounds away from zero, not upwards, so it turns that into -2, a negative order that looks like a typo and adds nothing sensible to the order total. MAX(0, B2-C2) replaces any surplus with 0 before the division, so the sugar and the rolled oats, which sit exactly at what the bake needs, both come back 0.