Financial
Tiered volume discounts: all-units XLOOKUP vs marginal block pricing
Calculate wholesale order totals under both all-units volume discounts (XLOOKUP match_mode -1) and progressive marginal slab tiers (SUMPRODUCT).
All-units volume discount vs marginal block/slab pricing
Wholesale catalogs use two distinct tier models that produce very different invoices! Under All-Units Volume Pricing (1–99 @ $10, 100–499 @ $8, 500+ @ $6), ordering 150 units prices ALL 150 units at $8 ($1,200). Notice the cliff: 95 units at $10 ($950) costs more than 100 units at $8 ($800).
Under Marginal Block (Slab) Pricing, the first 100 units always cost $10 ($1,000) and only the 50 units above 100 get the $8 rate ($400) -> $1,400 total, eliminating pricing cliffs.
| MinQty (A) | UnitPrice (B) | SlabDiff (C) | 150 Units All-Units | 150 Units Marginal Slab |
|---|---|---|---|---|
| 0 | 10 | 10 | — | 150 * 10 = 1,500 |
| 100 | 8 | -2 | 150 * 8 = 1,200 | 50 * (-2) = -100 (Net 1,400) |
| 500 | 6 | -2 | — | 0 * (-2) = 0 |
All-units XLOOKUP and marginal SUMPRODUCT formulas
XLOOKUP with match_mode = -1 finds the active volume bracket at or below OrderQty (D2).
=D2 * XLOOKUP(D2, A2:A4, B2:B4, B2, -1)Marginal slab / block pricing total
Store price drops (10, -2, -2) in Column C and apply SUMPRODUCT(--(Qty > MinQty), Qty - MinQty, SlabDiff).
=SUMPRODUCT(--(D2 > A2:A4), D2 - A2:A4, C2:C4)Inputs and interpretation
Use nonnegative quantities and unique ordered breakpoints beginning at zero. All-units thresholds apply to the entire quantity at the breakpoint, while marginal thresholds describe quantities already consumed: at 100 units the first 100 remain at 10 in the slab model. Define discrete versus fractional units explicitly. PriceDelta starts with the first unit price, then each difference from the preceding price. Taxes, minimum charges and rounding are separate policies.
Try the example
Use the original values or change them to test your understanding.