How to Calculate Tiered Shipping Costs in Excel

Price shipping by weight band with IFS, then layer on zone surcharges and a free-shipping threshold.

You will learn to

• Price a parcel from weight bands, including a per-kg overweight charge
• Apply a zone multiplier
• Add a free-shipping rule on top of the bands

How it works

Tiered shipping is a classic IFS job: test the weight bands from lightest to heaviest and return the price of the first band that fits. =IFS(B3<=1,4.99,B3<=5,9.99,B3<=20,19.99,TRUE,19.99+(B3-20)*1.5) charges flat rates up to 20 kg and adds $1.50 for every kilo over.

Real rate cards add more rules. Multiply by IF(C3="International",2,1) for a zone surcharge, and wrap everything in IF(AND(C3="Domestic",D3>=75),0, …) to give free shipping on qualifying orders. Building the rules in layers keeps each one easy to test.

=IFS(B3<=1,4.99,B3<=5,9.99,B3<=20,19.99,TRUE,19.99+(B3-20)*1.5)