Hollins Lane Kitchen orders from its wholesaler every Monday, in whole cases. The order sheet divided each shortfall by the pack size and rounded to the nearest case, so five lines out of ten arrived short of what the week needed.
| Item | Short by | Pack | ROUND sent | Units short |
|---|---|---|---|---|
| Oat milk 1L | 26 | 6 | 4 cases | 2 |
| Free-range eggs | 340 | 30 | 11 cases | 10 |
| Butter 250g | 95 | 40 | 2 cases | 15 |
| Paper napkins | 1,150 | 500 | 2 cases | 150 |
| Double cream 1L | 14 | 6 | 2 cases | 2 |
Scope.
ROUND,ROUNDUP,INT,QUOTIENT,MAX,MROUND,CEILING,COUNTIFandSUMPRODUCTwork in every current version of Excel.CEILING.MATHneeds Excel 2013 or later.In Excel 2007 and earlier,
CEILINGreturns#NUM!for a negative number with a positive pack size. Later versions round it towards zero instead.
01ROUND Orders the Nearest Case, Not Enough
The order sheet lists ten items in A2:D11: what the week needs in B, what is on the shelf in C and the case size in D. The old formula in E2, filled to E11:
=ROUND((B2-C2)/D2,0)
Monday's Order in Whole Cases
Hollins Lane Kitchen's ten-line wholesale order in A1:H11. E is the old ROUND formula and F the units it leaves over or short: a negative gap is a line that ran out. G is ROUNDUP on the shortfall clamped with MAX, and H the units left spare once it arrives.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
Oat milk is 26 cartons short and comes in sixes, so the sum is 4.33 cases. ROUND sends 4, which is 24 cartons. Any fraction under half a case rounds down, and that part of the gap is never ordered.
Lemons at 3.75 cases and cups at 2.6 round up, so those lines looked fine. Half the sheet was right by luck, which is why nobody suspected the formula.
Scenario: Enter 38 in B2, 12 in C2 and 6 in D2, then =ROUND((B2-C2)/D2,0) in E2. Check that it returns 4, and that =E2*D2 gives 24 cartons against a shortfall of 26.
02Each Way of Cutting a Number Orders Differently
Excel has several ways to turn 4.33 cases into a whole number, and only the ones that round up are safe for an order. The sugar row, where stock already exceeds need, shows a second trap.
| Formula | Oat milk, 26 ÷ 6 | Coffee, 22 ÷ 6 | Sugar, −200 ÷ 1000 |
|---|---|---|---|
ROUND(x,0) | 4 | 4 | 0 |
INT(x) | 4 | 3 | −1 |
QUOTIENT(units,pack) | 4 | 3 | 0 |
ROUNDUP(x,0) | 5 | 4 | −1 |
CEILING(units,pack)/pack | 5 | 4 | 0 |
INT and QUOTIENT count only full cases. That is the right answer to "how many full cases are on the shelf" and the wrong answer to "how many must I buy". ROUND is right about half the time.
Scenario: Enter =INT(22/6), =QUOTIENT(22,6) and =ROUNDUP(22/6,0) in three cells. Check that the first two return 3 and the last returns 4, the coffee order the café actually needs.
03ROUNDUP Always Covers the Shortfall
ROUNDUP with 0 digits moves any fraction up to the next whole number. 4.01 cases becomes 5, and so does 4.99. In G2, filled to G11:
=ROUNDUP((B2-C2)/D2,0)
Oat milk becomes 5 cases, eggs 12, butter 3, napkins 3 and cream 3. The five short lines now arrive with a few units spare, and the other lines do not change.
| Item | ROUND cases | ROUNDUP cases | Spare units |
|---|---|---|---|
| Oat milk 1L | 4 | 5 | 4 |
| Free-range eggs | 11 | 12 | 20 |
| Butter 250g | 2 | 3 | 25 |
| Paper napkins | 2 | 3 | 350 |
| Double cream 1L | 2 | 3 | 4 |
Five extra cases is the whole cost of never running out. The spare units go on the shelf and come off next Monday's shortfall.
Scenario: Enter =ROUNDUP((B2-C2)/D2,0) in G2 and fill it to G11. Check that G2 returns 5 and G3 returns 12, then enter =SUM(G2:G11) and check that it returns 44, not the 45 you expect.
04A Surplus Ordered Minus One Case
Sugar sachets need 800 and the shelf holds 1,000, so the shortfall is −200. ROUNDUP rounds away from zero, so −0.2 cases becomes −1, not 0.
The old ROUND hid this by landing on 0. The fix exposed it: G11 read −1, and the order total fell from 45 cases to 44 without anyone deleting a line.
Clamp the shortfall at zero before dividing. The corrected formula in G2, filled to G11:
=ROUNDUP(MAX(0,B2-C2)/D2,0)
MAX(0,B2-C2) turns any surplus into 0, so a line with enough stock orders nothing. With =ROUNDUP(MAX(0,B2-C2)/D2,0) in place, G11 returns 0 and the column totals 45.
Scenario: Enter 800 in B11, 1000 in C11 and 1000 in D11. Check that =ROUNDUP((B11-C11)/D11,0) returns −1, then wrap the gap in MAX(0,B11-C11) and check that it returns 0.
05CEILING Rounds Units Straight to the Pack
Sometimes the order goes in as units, not cases: 30 cartons rather than 5 cases. CEILING rounds a number up to the next multiple of the pack size, with no division:
=CEILING(MAX(0,B2-C2),D2)
It returns 30 for oat milk, 360 for eggs and 1,500 for napkins. Divide by D2 to get cases, and they match ROUNDUP on every row.
CEILING.MATH works the same way for positive numbers and is the modern name. Either way, keep the MAX: it states the rule outright instead of relying on how a negative number happens to round.
MROUNDrounds to the nearest multiple, so it has the same flaw asROUND.=MROUND(26,6)returns 24.
Scenario: Enter =CEILING(MAX(0,B2-C2),D2) in a spare column and fill it down. Check that it returns 30 for oat milk and 360 for eggs, then divide each by its pack size and compare with column G.
06Check That Every Line Covers the Need
A total cannot tell you that a line is short. Count the lines where the order falls below the gap:
=SUMPRODUCT(--(G2:G11*D2:D11<B2:B11-C2:C11))
It returns 0 for the fixed column. Point it at the old ROUND column E and it returns 5, the five lines that ran out.
A second check catches the opposite mistake, a negative order:
=COUNTIF(G2:G11,"<0")
It returns 0 once the MAX is in, and 1 on the first ROUNDUP fix. Put both checks above the order, so they are seen before it is sent.
Scenario: Enter the SUMPRODUCT check in J2 and point it at column E. Check that it returns 5, then change E2:E11 to G2:G11 and check that it returns 0.
07Five Traps
- Rounding to the nearest case.
ROUNDandMROUNDdrop any fraction under a half. An order needsROUNDUPorCEILING. - Counting full cases with
INTorQUOTIENT. They answer what is on the shelf, not what to buy, and they cut 3.67 cases to 3. - Letting a surplus go negative.
ROUNDUPturns −0.2 into −1. Wrap the gap inMAX(0,...)first. - Rounding need and stock separately.
=ROUNDUP(B5/D5,0)-ROUNDUP(C5/D5,0)returns 3 cases of coffee for a 22-bag gap. Subtract first, then round once. - Checking only the total. 40 cases looks like a normal week. Count short lines with
SUMPRODUCT, row by row.
08Four Exercises
- Reproduce it. Build the sheet with
ROUNDand confirm that five lines arrive short. - Fix it. Switch to
ROUNDUPand confirm that the order rises from 40 cases to 44, with sugar at −1. - Clamp it. Add
MAX(0,...)and confirm that sugar returns 0 and the order totals 45. - Go by units. Rebuild column G with
CEILINGon the units and confirm that every row matches.
What to Take Away
Every need, every stock count and every pack size on the café's sheet was right. ROUND ordered the nearest case, so any shortfall under half a case was never bought.
Order with ROUNDUP or CEILING, clamp surpluses with MAX(0,...), and count short lines before the order goes out.