Inventory Reorder Flag & Quantity

Excel Formulas › Business

All versionsIF

Never run out, never over-order. Compare stock on hand to a reorder point and flag what needs buying — then compute exactly how much to bring each item back up to its target level.

Quick formula: for stock in B2 and reorder point in C2:
=IF(B2 <= C2, "REORDER", "OK")
When stock falls to or below the reorder point, the row flags REORDER. Add a quantity formula to say how much.

Functions used (tap for the full reference guide):

IFMAX

The example

Stock checked against each item’s reorder point and target.

ABCD
1ItemStockReorder ptFlag
2Bolts820REORDER
3Nuts4520OK
4Washers1515REORDER

The formula

Flag low stock, then size the order:

=IF(B2<=C2, "REORDER", "OK") =IF(B2<=C2, MAX(0, target - B2), 0) // order qty

How it works

A threshold test drives both the flag and the quantity:

  1. The flag compares stock to the reorder point: =IF(B2<=C2, "REORDER", "OK").
  2. The order quantity brings the item up to a target level: =IF(B2<=C2, target - B2, 0).
  3. Wrap the quantity in MAX(0, …) so a stock level above target never produces a negative order.
  4. Filter or conditionally format the REORDER rows to build an instant purchase list.

Smarter reorder points factor in lead time and demand: reorder point = average daily usage × lead-time days + safety stock. Put usage and lead time in columns and compute the reorder point itself with a formula so it adapts as demand changes.

Try it: interactive demo

Live demo

Set stock, reorder point, and target.

Status · Order

Variations

Order quantity

Bring up to target:

=IF(B2<=C2, MAX(0, target-B2), 0)

Computed reorder point

From usage and lead time:

=dailyUse * leadDays + safetyStock

Days of stock left

Runway at current usage:

=B2 / dailyUse

Pitfalls & errors

≤ vs <. Decide whether hitting the reorder point exactly should trigger an order. <= includes it; < waits one more unit.

Clamp the order quantity. Without MAX(0, …), an item already above target yields a negative order.

Static reorder points go stale. If demand shifts, a fixed reorder point over- or under-orders. Recompute it from recent usage periodically.

Practice workbook

📊
Download the free Inventory Reorder Flag & Quantity practice workbook
An inventory sheet with reorder flag, order-quantity, computed-reorder-point, and days-of-stock variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I flag items that need reordering in Excel?
Compare stock to the reorder point: =IF(stock<=reorderPoint, "REORDER", "OK"). Filter the REORDER rows for a purchase list.
How do I calculate how much to order?
Bring stock up to a target level: =IF(stock<=reorderPoint, MAX(0, target-stock), 0). The MAX(0,...) prevents negative orders.
How do I set a reorder point?
A common formula is averageDailyUsage × leadTimeDays + safetyStock, so the point adapts to demand and supplier lead time.

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: IF with AND/OR · Min if criteria · Highlight entire row

Function references: IF · MAX