Level 4 · Shipping fees with IF

So far every formula did the same arithmetic on every row. Business rules are different: they depend on the row. “Free shipping from $75” means one result for some orders and another for the rest. That is what IF is for.

The three parts of IF

=IF(test, value_if_true, value_if_false)

• test: a comparison that is either TRUE or FALSE, like C2>=75;
• value_if_true: what the cell shows when the test is TRUE;
• value_if_false: what it shows otherwise.

For the first order (subtotal in C2), the shipping fee is:

=IF(C2>=75,0,7.95)

Read it as a sentence: “if the subtotal is at least 75, shipping is 0, otherwise 7.95”.

Comparison operators

Operator	Means
=	equal to
<>	not equal to
> / <	greater / less than
>= / <=	greater / less than or equal to

The difference between > and >= is the edge of the rule. “From $75” includes $75 itself, so the test is >=. When you translate a rule into a formula, ask what happens exactly at the threshold; that is where most disputes with customers come from.

Text comparisons work too, with the text in double quotes: =IF(B2="web","Online","Other"). They ignore upper and lower case, so "Web" matches web.

Keep the rule in cells

The sheet already holds the rule’s inputs: the threshold in G1 and the standard fee in G2. Point at them instead of typing 75 and 7.95 into the formula, and lock them with $ as in the last level, because every row reads the same two cells:

=IF(C2>=$G$1,0,$G$2)

Filled down to row 38, each row tests its own subtotal (C3, C4…) against the same threshold. When marketing tries “free shipping from $60” next month, Rachel changes G1 and every fee updates.

Checking the result

Once the column is filled, scan it. Orders such as 2015 (35.98) and 2028 (29.99) should show 7.95; big orders should show 0. A quick sanity check like this catches a reversed test, where < was typed instead of >= and every fee lands on the wrong orders.

Common mistakes

• Reversing the outcomes. =IF(C2>=$G$1,$G$2,0) charges the fee to exactly the orders that should ship free.
• Quotes around numbers. "0" and "7.95" are text, not numbers. They look right but break any SUM of the column.
• Unlocked inputs. =IF(C2>=G1,0,G2) is right in D2 and wrong from D3 down, where the test reads G2 and the fee reads the empty G3.