Math & Statistical
Check & Count Exact Multiples of a Specific Pack or Lot Size (MOD = 0)
Test whether order quantities are exact multiples of case pack size N using MOD(qty, N) = 0, flag broken-case remainders, and count full-case orders across a table.
Catching broken-case order quantities before warehouse fulfillment rejects the PO!
Wholesale distribution, CNC bar-stock cutting, and sterile vial packaging enforce strict case-pack multiples (e.g., 12 units per master carton or 25 vials per tray). Dividing order quantity B2 by pack multiple $E$2 inside MOD(B2, $E$2) returns the exact leftover unit remainder: 0 means an exact full-carton multiple (48 / 12 = 4 cartons, remainder 0), whereas any positive remainder (50 -> remainder 2) flags a broken case immediately!
Need to know how many units to ADD to round a broken-case order UP to the next full carton? MOD(-B2, $E$2) (or CEILING(B2, $E$2) - B2) computes the exact top-up units in one step!
| Order Qty (Pack Size = 12) | Remainder MOD(Qty, 12) | Full-Case Check | Units Needed for Next Full Case |
|---|---|---|---|
| 48 units (4 Cases) | 0 | "Exact Pack Multiple" | 0 units |
| 50 units (4 Cases + 2) | 2 | "Broken Case (+2 Loose)" | 10 units (to reach 60) |
| Pack Size <= 0 | Divide-by-Zero Guard | "Invalid Pack Size" | Guard Before MOD |
Exact Pack-Multiple Validator (MOD = 0)
Checks that pack size $E$2 > 0 and order quantity B2 > 0 before testing MOD(B2, $E$2) = 0.
=IF(OR($E$2 <= 0, B2 <= 0), "Invalid Pack or Qty", IF(MOD(B2, $E$2) = 0, "Exact Pack Multiple", "Broken Case"))Count Orders That Are Exact Multiples of Pack Size Across Range
Counts rows in helper remainder column C2:C6 where MOD(qty, pack) equals 0.
=COUNTIF(C2:C6, 0)Try the example
Use the original values or change them to test your understanding.