Mathematics Standard • Year 11 • Module 1 • Lesson 6
Formula Modelling with Spreadsheets
Build fluency reading cell references, translating spreadsheet formulas into words and calculating row outputs.
1. Quick recall
Answer each question in the space provided. 1 mark each
Q1.1 What does a cell reference such as B2 stand for in a spreadsheet?
Q1.2 Translate the spreadsheet formula =B2*C2 into words.
Q1.3 A spreadsheet uses =B2*C2+D2. State which symbol (* or +) tells Excel to multiply and which tells it to add. multiply = ____________ add = ____________
2. Worked example — interpret a canteen order formula
Follow each line of working. The cell formula behaves exactly like the algebra version.
Problem. A canteen spreadsheet uses =B2*C2+D2 where B2 = quantity, C2 = cost per item, D2 = delivery fee. Find the total when B2 = 12, C2 = 4, D2 = 10.
Step 1 — Write the algebra version.
Total = quantity × cost + delivery or T = qc + f
Reason: * is multiplication, + is addition. Each cell letter stands for one input variable.
Step 2 — Substitute the row values.
T = 12 × 4 + 10
Reason: B2 = 12 (qty), C2 = 4 (cost), D2 = 10 (delivery). Copy each value into its place.
Step 3 — Apply order of operations (multiply before adding).
T = 48 + 10 = 58
Reason: 12 × 4 = 48, then add the $10 delivery fee.
Conclusion. The order total is $58.
3. Faded example — fill in the missing steps
A sports store row uses =B3*C3 where B3 = quantity of balls and C3 = price per ball. Fill in each blank for B3 = 8 and C3 = 12. 4 marks
Step 1 — Words version:
Total = ____________ × ____________
Step 2 — Substitute the values:
Total = ____ × ____
Step 3 — Calculate:
Total = ____________
Conclusion sentence. The total cost of the row is $____________.
4. Graduated practice — interpret, write and copy formulas
Show working below each part. Always write a units sentence for any dollar or quantity answer.
Foundation — single-row substitution (4 questions)
| Q | Problem | Answer |
|---|---|---|
| 4.1 1 | Calculate =B2*C2 when B2 = 6 and C2 = 5. | |
| 4.2 1 | Calculate =B2+C2 when B2 = 18 and C2 = 9. | |
| 4.3 1 | Calculate =B2*C2+D2 when B2 = 4, C2 = 7 and D2 = 12. | |
| 4.4 1 | Calculate =B2*C2+D2 when B2 = 10, C2 = 2.50 and D2 = 5. |
Standard — typical HSC difficulty (6 questions)
Translate each formula into words OR substitute, depending on the prompt.
4.5 Translate =B2*C2+E2 into words if B2 is hours, C2 is hourly rate and E2 is a booking fee. 2 marks
4.6 Calculate the output of =B2*C2+E2 when B2 = 5, C2 = 22 and E2 = 30. 2 marks
4.7 Write a spreadsheet formula for total material cost if B2 is metres used and C2 is cost per metre. 2 marks
4.8 A row uses =B2*C2+D2 with B2 = 18 (quantity), C2 = $3 (unit cost), D2 = $12 (delivery). Find the total. 2 marks
4.9 A formula =B3*C3 calculates the total for row 3. Write the equivalent formula for row 5. 1 mark
4.10 A formula T = 8n + 15 models hire cost for n items. Use it to find T when n = 14 and when n = 10. State by how much the total changes. 3 marks
Extension — design and unit-consistency (2 questions)
4.11 A pay spreadsheet uses =B2*C2+D2 where B2 = hours worked, C2 = hourly rate, D2 = fixed allowance. Write the algebra version using letters P, h, r, a. 2 marks
4.12 A canteen table records "Price per item" in column C. Row 2 has C2 = 4 (dollars). Someone enters row 3 as C3 = 250 (cents). Explain what would go wrong if the same =B2*C2 formula is copied down. 3 marks
5. Self-check the easy 3
Tick the first three once you've checked your method works.
How did this worksheet feel?
What I'll revisit before next class:
Q1.1 — Meaning of B2
B2 is a cell reference. It points to the value stored in column B, row 2 — one input the formula uses.
Q1.2 — =B2*C2 in words
"Multiply the value in B2 by the value in C2." (e.g. quantity times price per item.)
Q1.3 — Symbols
multiply = * add = +.
Q3 — Faded row formula
Step 1: Total = quantity × price per ball.
Step 2: Total = 8 × 12.
Step 3: Total = 96.
Conclusion: The total cost of the row is $96.
Q4.1 — =B2*C2 with B2 = 6, C2 = 5
6 × 5 = 30.
Q4.2 — =B2+C2 with B2 = 18, C2 = 9
18 + 9 = 27.
Q4.3 — =B2*C2+D2 with B2 = 4, C2 = 7, D2 = 12
4 × 7 + 12 = 28 + 12 = 40.
Q4.4 — =B2*C2+D2 with B2 = 10, C2 = 2.50, D2 = 5
10 × 2.50 + 5 = 25 + 5 = $30.00.
Q4.5 — Translate =B2*C2+E2
"Multiply hours by hourly rate, then add the booking fee." (Total cost = hours × rate + booking fee.)
Q4.6 — Calculate =B2*C2+E2
5 × 22 + 30 = 110 + 30 = $140.
Q4.7 — Material cost formula
=B2*C2 (i.e. metres used × cost per metre).
Q4.8 — Canteen total with delivery
18 × 3 + 12 = 54 + 12 = $66.
Q4.9 — Copy formula to row 5
=B5*C5. (Every row number in the formula bumps to the new row.)
Q4.10 — T = 8n + 15 with n = 14 and n = 10
T(14) = 8(14) + 15 = 127. T(10) = 8(10) + 15 = 95. The total changes by +$32 (because 4 extra items at $8 each).
Q4.11 — Pay formula in algebra
P = hr + a (Pay = hours × rate + fixed allowance.)
Q4.12 — Mixed units sabotage the formula
Row 2 uses dollars (4 = $4), row 3 uses cents (250 = $2.50). If =B3*C3 multiplies the quantity by 250, the row 3 total comes out 100 times too large (cents not dollars). Every column must use the same units, or the formula must divide by 100 to convert.