Build Three-Tier Results with Nested IF Statements

Excel & Google Sheets

How it works

The second IF sits in the first IF's "otherwise" slot. The formula tests B2>=100 first; if that's true it returns 4.50 and stops. Only when the order is under 100 does the inner IF run, testing B2>=50 for the 5.25 tier, with 6.00 as the final fallback. Because each test only sees what the previous one rejected, the conditions must run from strictest to loosest — and the inner test doesn't need an upper bound, since anything 100+ never reaches it. Past three or four tiers, nesting gets hard to read: switch to IFS (Excel 2019+ and Google Sheets), which lists condition/result pairs flat.

B2>=100,4.5

The strictest test first. 100 units or more gets the bulk price and the formula stops.

IF(B2>=50,5.25

Runs only for orders under 100 — so it alone defines the 50–99 tier.

,6)

The final fallback: anything under 50 pays list price.

When to use it

Use it for tiered unit pricing, shipping rates by weight band, discount levels by order size, or commission brackets — anywhere a number maps to three or four outcomes and IFS isn't available.

Common mistakes

• ✕Loosest condition first, so the top tier never fires.

  ✓=IF(B2>=50,5.25,IF(B2>=100,4.5,6)) prices a 200-unit order at 5.25, because B2>=50 catches it first. Order the tests strictest to loosest.

• ✕Boundary quantities priced in the wrong tier.

  ✓With >= tests, exactly 100 gets 4.50 and exactly 50 gets 5.25. If the deal sheet says "over 100," use B2>100 instead — check the wording before choosing.

• ✕Nesting five or more tiers into an unreadable formula.

  ✓Every extra IF adds a parenthesis to misplace. From four tiers up, use IFS — =IFS(B2>=100,4.5,B2>=50,5.25,TRUE,6) — or a VLOOKUP against a rate table.