01 / 09ROUND Orders the Nearest Case, Not Enough

RoundingCEILING & MROUNDFormula Auditing

Round Up to Case Packs in Excel With ROUNDUP and CEILING

Five Lines of the Café's Order Arrived Short, Because ROUND Bought the Nearest Case Instead of Enough Cases

13 Nov 20267 min read

UsesCEILING, FLOORROUNDMIN, MAXQUOTIENT

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.

ItemShort byPackROUND sentUnits short
Oat milk 1L2664 cases2
Free-range eggs3403011 cases10
Butter 250g95402 cases15
Paper napkins1,1505002 cases150
Double cream 1L1462 cases2

Scope. ROUND, ROUNDUP, INT, QUOTIENT, MAX, MROUND, CEILING, COUNTIF and SUMPRODUCT work in every current version of Excel. CEILING.MATH needs Excel 2013 or later.

In Excel 2007 and earlier, CEILING returns #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.

ABCDEFGH
1
Item
Needed
In stock
Pack size
ROUND cases
Gap (units)
Cases needed
Spare (units)
2
Oat milk 1L
38
12
6
4
-2
5
4
3
Free-range eggs
420
80
30
11
-10
12
20
4
Butter 250g
120
25
40
2
-15
3
25
5
Coffee beans 1kg
30
8
6
4
2
4
2
6
Sourdough loaves
64
0
8
8
0
8
0
7
Paper napkins
1500
350
500
2
-150
3
350
8
Double cream 1L
20
6
6
2
-2
3
4
9
Takeaway cups
3000
400
1000
3
400
3
400
10
Lemons
50
5
12
4
3
4
3
11
Sugar sachets
800
1000
1000
0
200
0
200

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.

Try it in the grid

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.

FormulaOat milk, 26 ÷ 6Coffee, 22 ÷ 6Sugar, −200 ÷ 1000
ROUND(x,0)440
INT(x)43−1
QUOTIENT(units,pack)430
ROUNDUP(x,0)54−1
CEILING(units,pack)/pack540

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.

Try it in the grid

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.

ItemROUND casesROUNDUP casesSpare units
Oat milk 1L454
Free-range eggs111220
Butter 250g2325
Paper napkins23350
Double cream 1L234

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.

Try it in the grid

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.

Try it in the grid

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.

MROUND rounds to the nearest multiple, so it has the same flaw as ROUND. =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.

Try it in the grid

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.

Try it in the grid

07Five Traps

  1. Rounding to the nearest case. ROUND and MROUND drop any fraction under a half. An order needs ROUNDUP or CEILING.
  2. Counting full cases with INT or QUOTIENT. They answer what is on the shelf, not what to buy, and they cut 3.67 cases to 3.
  3. Letting a surplus go negative. ROUNDUP turns −0.2 into −1. Wrap the gap in MAX(0,...) first.
  4. 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.
  5. Checking only the total. 40 cases looks like a normal week. Count short lines with SUMPRODUCT, row by row.

08Four Exercises

  1. Reproduce it. Build the sheet with ROUND and confirm that five lines arrive short.
  2. Fix it. Switch to ROUNDUP and confirm that the order rises from 40 cases to 44, with sugar at −1.
  3. Clamp it. Add MAX(0,...) and confirm that sugar returns 0 and the order totals 45.
  4. Go by units. Rebuild column G with CEILING on 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.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge