Level 5 · Boss: build the order-total sheet

One sheet, three steps

Real spreadsheets are chains: each block of cells feeds the next. This boss level rebuilds an order total the way a support lead would on a call, using everything from the chapter: cell references and brackets, SUM over a range, IF for a rule, and inputs kept in labelled cells.

The sheet holds the four lines of order 7502 in A1:D5, and the store’s rules in column H: the sales tax rate in H1, the free-shipping threshold in H2 and the standard fee in H3. Rows 8 to 11 are the summary you fill in.

Step 1: line totals

Each line is quantity times price, minus its discount. Every item in this order has 25% off:

E2   =B2*C2*(1-D2/100)

Write it once and fill it down to E5. Because all references are relative, E3 reads row 3, E4 row 4. Check one line by hand: 2 × 18.99 × 0.75 = 28.485, which the cell shows as 28.49.

Step 2: the subtotal

B8   =SUM(E2:E5)

Sum the line totals, never the list prices. The discount lives inside each line total, so this subtotal is what the customer owes for the goods.

Step 3: shipping, tax and the total

The shipping rule is the same one support quotes every day: free from $75, otherwise the standard fee. The test reads the subtotal after discounts:

B9   =IF(B8>=H2,0,H3)
B10  =B8*H1
B11  =B8+B9+B10

These formulas are not filled down, so the inputs don’t need dollar signs here. Lock them anyway if you plan to copy the block to another order; it costs nothing and prevents the classic fill-down slip.

The total shows 84.23, while 70.46 + 7.95 + 5.81 adds up to 84.22. Nothing is wrong: cells keep more decimals than they show. The subtotal is really 70.4625 and the tax 5.8131, and the sheet adds the full values. If a receipt rounds each part to cents first, wrap them in ROUND(…, 2) so the sheet matches it to the cent.

Thinking like an analyst

This order is a good example of why the order of rules matters. At list price the four lines add up to more than $75, so a quick glance says “free shipping”. After the 25% discount the subtotal is 70.46, under the threshold, so the customer correctly paid 7.95. The sheet turns an argument into a calculation anyone can follow.

Before you build a sheet like this, write the rules down with the person who owns them:

• Is shipping judged before or after discounts? (Here: after.)
• Is tax charged on shipping too? (Here: no, on the goods only.)
• What happens at exactly $75? (Here: free, so >=.)

Common mistakes

• Testing shipping against the list-price total instead of B8.
• Adding the tax rate instead of multiplying: =B8+H1 adds 0.0825 dollars.
• Typing the subtotal into B9’s test (=IF(70.46>=75,…)): the sheet stops working for the next order.
• Summing C2:C5 (prices) in step 2, which ignores quantities and discounts.