How to write a nested IF in Excel

Start with one IF, then nest the next

Write the rule in plain words first: orders of $100 or more ship free, orders of $50 or more pay $4.99, everything else pays $9.99.

The first IF handles free shipping. Its false branch is where the rest of the rule goes, so you drop a second IF there. Type the whole thing, then count: two IFs means two closing brackets at the end.

1. Type =IF(B2>=100,0, for the first band.
2. Instead of a final value, type IF(B2>=50,4.99,9.99) for the remaining bands.
3. Close the outer IF with one more ) and press Enter.
4. Double-click the fill handle to copy it down the column.

Why order matters

Excel stops at the first TRUE condition. If you test the loose condition first, the strict one never gets a chance. This formula is valid but wrong: a $134 order passes B2>=50 first and is charged $4.99 instead of shipping free.

Rule of thumb: when testing "greater than", go from the highest threshold down. When testing "less than", go from the lowest up.

Going deeper: four or more levels

Each extra band adds one more IF in the last false slot. Excel allows up to 64 nested IFs, but anything past four or five gets hard to read and easy to break.

Here is a four-band version. Notice the shape: the final value 9.99 sits at the very end, followed by one closing bracket per IF.

Cleaner alternative: IFS (Excel 2019 and later)

IFS takes condition and result pairs side by side, so there are no brackets to count. End with TRUE so anything that matches nothing still gets a value.

Best for many bands: a lookup table

Put the thresholds in a small table (D1:E4 here, sorted from smallest to largest) and look up the total with an approximate match. Changing a price now means editing a cell, not a formula.

XLOOKUP with match mode -1 finds the exact value or the next smaller one. In older Excel, =VLOOKUP(B5,D2:E4,2,TRUE) does the same thing as long as the table is sorted ascending.