Vending: Restock Quantity to Par

Excel Formulas › Vending

All versions

A route driver refills each slot back to its par. Subtract what is left from the par level — floored at zero — and the pick list writes itself.

Quick formula: Par level minus current stock, never below zero:
=MAX(B2-C2,0)

A slot with a par of 24 and 9 left needs 15 units. MAX keeps an overstocked slot at 0 rather than a negative.

Functions used (tap for the full reference guide):

MAX

The example

Three slots with their par level and current count.

ABCD
1ItemParCurrentRestock
2Cola24915
3Water362016
4Chips18180

The formula

Subtract, then floor at zero:

=MAX(B2-C2,0) // par - on hand, never negative

How it works

The refill is the gap back up to par:

  1. B2-C2 is how far below par the slot has dropped.
  2. MAX(…,0) floors it at zero so a full or overstocked slot shows 0, not a negative number.
  3. The result is exactly how many to load on this stop.

Sum the Restock column to size the pick for the whole machine, or the whole route.

Try it: interactive demo

Interactive

Enter the par level and the current count.

Variations

Round to case packs

Round the refill up to whole cases of a given pack size.

=ROUNDUP(MAX(B2-C2,0)/CasePack,0)*CasePack

Pitfalls & errors

Without MAX, an overstocked slot returns a negative — meaningless on a pick list. The floor at 0 keeps it clean.

Count current stock at the moment of restock; stale counts under- or over-fill the slot.

Practice workbook

📊
Download the free Vending: Restock Quantity to Par practice workbook
Edit the yellow Par and Current cells; the Restock column floors at zero.

Frequently asked questions

Why floor at zero?
An overstocked or full slot would otherwise show a negative restock, which makes no sense on a pick list. MAX(...,0) keeps it at zero.
Can I total a route?
Yes — sum the Restock column per machine, then across machines, to build the full route pull.

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

Related formulas: Running Total · Self-Storage: Monthly Revenue

Function references: MAX