Order 260 pairs of rental skates split across five size runs and you hit the oldest problem in allocation: the rounded parts do not add up to the whole. Here they come to 259. The fix is not to fudge the shares — it is to let the largest line absorb the difference, which is both defensible and one formula.
Five size runs against 260 pairs round to 259. The Adult 7–9 line takes the extra pair and the order ties exactly.
The example
A 260-pair order split across the size curve a rink actually sees on a Saturday.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Size run | Share | Raw pairs | Rounded | Final order |
| 2 | Youth 1-3 | 12% | 31.2 | 31 | 31 |
| 3 | Youth 4-6 | 17% | 44.2 | 44 | 44 |
| 4 | Adult 7-9 | 35% | 91.0 | 91 | 92 |
| 5 | Adult 10-12 | 27% | 70.2 | 70 | 70 |
| 6 | Adult 13-15 | 9% | 23.4 | 23 | 23 |
| 7 | TOTAL | 100% | 260.0 | 259 | 260 |
The formula
Two columns and one exception, and the exception is the whole point:
How it works
Why the plug line is the right answer rather than a hack:
$B$11*B5is the raw allocation — the order total times that size's share of the curve. Keep the total in one absolute cell so a change to the order re-splits everything at once.ROUND(...,0)gives whole pairs, because you cannot buy 31.2 skates. Each line rounds independently, and independent rounding is exactly why the column no longer sums to 260.- The plug line takes the remainder: the order total less every other rounded line. Choose the largest share for it — a one-pair adjustment on a 91-pair line is noise, the same adjustment on a 23-pair line is a 4% distortion.
- Confirm the total row equals the order. If it does not, either a share is wrong or the plug formula is not excluding itself, which is the classic circular-reference version of this mistake.
Check the shares sum to exactly 100% before anything else. A curve that sums to 99% will look like a rounding problem and is actually a data problem.
Try it: interactive demo
Enter an order total and a size share to see the raw, rounded and drift figures.
Variations
Spread the drift instead of plugging one line
When the drift is larger than a pair or two, add one to each of the largest remainders in turn rather than dumping it all on a single size.
Rebuild the curve from last season
Derive shares from actual rentals rather than a guess: each size's rentals over total rentals is the curve you should be ordering to.
Pitfalls & errors
Do not point the plug formula at a range that includes itself — =Total-SUM(D5:D9) on row 7 is a circular reference. Sum the two ranges either side of the plug line instead.
Rounding each line independently can drift by more than one unit when there are many lines. With a dozen size runs the drift can reach three or four pairs, at which point spreading it across the largest remainders is fairer than plugging.
Rental fleets skew smaller than retail size curves because children rent and adults often own. Build the curve from your own rental log, not from a manufacturer's retail distribution.
Practice workbook
Frequently asked questions
Which line should be the plug?
Can I just round the shares so they add up?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class