Tiered & Banded Calculations in Excel: All-Units vs Incremental Tiers, Block Tariffs, Volume Rebates, Stepped Costs & Band Lookups

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Look up the band and the unit price of six orders on Larkspur's price list with an approximate-match XLOOKUP and INDEX/MATCH, then check a partner's hand-typed band table for gaps and overlaps.
• Price six orders on the all-units list, measure the saving at each threshold, find the break-even quantity from which ordering the band start is cheaper, and return the cheapest cost for at least each quantity.
• Split Halden Legal's 2,750 archive boxes into Larkspur's four incremental tiers, calculate the whole charge in one SUMPRODUCT, return the effective rate per box and compare it with an all-units charge at the same rates.
• Split the water usage of Larkspur's six sites into the tariff's three blocks with one copyable formula, calculate each monthly bill, return the marginal price of the next m³ and find the site that pays the highest average price.
• Calculate Larkspur's expected paper rebate from Orrin Paperworks on a retroactive and on a prospective scale, accrue the rebate on the purchases so far at the rate the year-end forecast will reach, and measure the gap to the next threshold.
• Pay five deals on Larkspur's incremental commission plan with an accelerator, return the effective commission %, price four web orders on a fictional platform's all-units fee table and spot the order that pays more than a larger one.
• Staff Larkspur's warehouse for seven daily volumes with ROUNDUP, cost each day with its site, picker and packing costs, return the cost per order line and find the volume that runs cheapest per line.
• Build one copyable tier formula on a horizontal band table, cost six customers on two fictional envelope price lists, label the cheaper list per customer and chart both costs to see where the lists cross.