How to Automatically Distribute Remaining Pairs Across Packing Cases in Excel
Question details
The user needs an Excel formula to automatically allocate leftover shoe pairs across packing cases with a fixed capacity, eliminating the need for manual distribution.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Distributing inventory, specifically shoe pairs, into boxes with a fixed maximum capacity and handling the remaining leftover items evenly across the initial cases.
- Observed behavior
- The user wants an automated formula approach rather than manually counting and assigning remaining pairs to individual packing cases.
Ensure your spreadsheet is organized with distinct columns for Total Pairs, Case Capacity, and sequential Case Numbers (1, 2, 3...) to accurately reference these values in your formula.
Use QUOTIENT, MOD, and IF Functions
This method works across all Excel versions, including Excel 2007, by calculating the base case quantity and evenly distributing the remainder.
The QUOTIENT function calculates the base number of items each case should hold. The MOD function calculates the exact remainder of leftover pairs. By nesting these with an IF function, Excel will automatically allocate exactly one extra leftover pair sequentially to the first few cases until the remainder is exhausted.
Create headers in your worksheet for 'Total Pairs', 'Case Capacity' (e.g., 10), and 'Case Number' (sequentially numbered 1, 2, 3, etc.). Ensure 'Total Pairs' and 'Case Capacity' are placed in fixed cells, such as A2 and B2.
Select the target cell for Case 1 (e.g., D2) and input the formula: =QUOTIENT($A$2,$B$2) + IF(C2<=MOD($A$2,$B$2),1,0). Here, C2 refers to the current Case Number. The absolute references ($A$2 and $B$2) lock the total numbers and capacity.
Click the cell containing the new formula, hover over the bottom-right corner until the cursor becomes a cross, and drag the fill handle down to apply it to all the remaining case numbers.

Distribute Inventory Easily with WPS Office
WPS Spreadsheet fully supports advanced logical and mathematical functions like QUOTIENT and MOD. You can seamlessly automate your inventory distribution and packing tasks using the exact same formulas.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your existing inventory spreadsheet or create a new one.
- 2. Input the allocation formula: Click on the cell next to your first Case Number and type the exact same formula: =QUOTIENT(TotalPairs,CaseCapacity)+IF(CaseNumber<=MOD(TotalPairs,CaseCapacity),1,0).
- 3. Drag to fill: Use the fill handle at the bottom right of the active cell to drag the formula across all cases, automatically calculating the distributed remainder.

Frequently Asked Questions
Why is the QUOTIENT function returning a #NAME? error in Excel 2007?
In Excel 2007 and earlier, the QUOTIENT function requires the Analysis ToolPak add-in to be active. Click the Microsoft Office Button, go to Excel Options > Add-Ins, select Excel Add-ins, and check the box for Analysis ToolPak.
How does the MOD function help in distributing leftover inventory?
The MOD function calculates the exact remainder of items after dividing the total by the case capacity. By combining it with an IF statement, you can ensure that each case numbered less than or equal to the remainder receives exactly one extra item.
Can I use dynamic arrays to distribute items in newer versions of Excel?
Yes. If you are using Excel 365 or Excel 2021, you can use modern functions like SEQUENCE and LET to output the entire distribution list dynamically from a single cell, avoiding the need to drag formulas manually.




