Level 1 · Three shipping bands with IFS

In chapter 1, one IF handled a rule with two outcomes: free shipping or the standard fee. Real rules often have more. Megan’s test has three bands, and the sheet holds them as a small table: F2:G4 lists where each band starts and its fee.

Nested IF: one IF inside another

An IF has room for only two outcomes, so the classic way to get three is to put a second IF in the “otherwise” slot of the first:

=IF(C2>=$F$2,$G$2,IF(C2>=$F$3,$G$3,$G$4))

Read it from the left: if the subtotal reaches $75, the fee is G2; otherwise ask again, and if it reaches $35 the fee is G3; otherwise G4. It works, and you will see it in many old workbooks. With five or six bands it turns into a wall of brackets where one misplaced ) changes the answer.

IFS: pairs, read from the left

IFS writes the same rule as a flat list of pairs, a test followed by the value for that test:

=IFS(C2>=$F$2,$G$2,C2>=$F$3,$G$3,TRUE,$G$4)

Pair	Means
C2>=$F$2,$G$2	from $75: fee 0
C2>=$F$3,$G$3	from $35: fee 4.95
TRUE,$G$4	anything else: fee 7.95

IFS checks the tests from left to right and returns the value of the first test that is TRUE. It never looks at the rest. That is what makes the order of the pairs part of the rule.

Order the tests from the top band down

Order 6002 (row 3) has a subtotal of 90.96. It passes both >=75 and >=35. Because the $75 test comes first, IFS returns 0 and stops. Swap the first two pairs and the same order matches >=35 first and pays 4.95. So does every order of $75 or more, and the free band never fires.

When bands are written as “from” amounts, start with the highest and work down. Each later test only sees the orders that failed every test before it.

The catch-all TRUE

IFS has no “otherwise” slot. If no test is TRUE, it returns #N/A. Order 6001 (18.99) fails both band tests, so without the last pair D2 shows #N/A. The fix is a final test that is always TRUE: the word TRUE itself. It catches everything left over, so F4 in the band table documents the bottom band without being needed in the formula.

Checking the result

Filled down to row 40, the column holds 30 free orders, 4 at 4.95 and 5 at 7.95. Look at the edges: order 6008 at 73.98 (row 9) pays 4.95, and order 6021 at 77.96 (row 22) ships free. If those two look right, the >= tests point the right way. A SUM of the column, 59.55, is what this test would have charged on these 39 orders.

Common mistakes

• Testing the lowest band first. C2>=$F$3 before C2>=$F$2 catches the big orders too early, and they pay 4.95.
• No catch-all. Small orders show #N/A instead of the standard fee.
• > instead of >=. An order of exactly $35.00 or $75.00 lands in the wrong band.
• Unlocked band cells. F2 filled down becomes F3, F4, and the bands drift row by row. Lock every cell of the band table with $.