Formula Modelling with Spreadsheets
Connect algebra formulas to spreadsheet-style calculations by identifying inputs, fixed values, variable values and repeated formulas. A spreadsheet is just algebra organised into a table.
Practise this lesson
Three printable worksheets that build from foundations to mastery — or build your own from any module’s questions.
A school canteen spreadsheet uses quantity, price per item and a fixed delivery fee to calculate order totals.
Without calculating — what numbers should change from row to row, and what might stay fixed? Type the values that should change and stay fixed.
A spreadsheet formula uses cell references instead of variable names, but the underlying algebra is identical. Change an input cell and every formula that references it updates automatically.
Cell references (like B2, C2) are the spreadsheet's variable names. A copied formula repeats the same relationship across rows — just as an algebra formula applies for any value of the variable.
Key facts
- A spreadsheet formula is a rule written using cell references or values.
- Inputs are values used by a formula to calculate an output.
- A copied formula repeats the same relationship across rows.
Concepts
- Spreadsheet formulas are algebra formulas in a table format.
- Changing an input changes the output according to the formula.
- Units must stay consistent across a spreadsheet table.
Skills
- Describe a spreadsheet formula in words.
- Write a cell-style formula for repeated calculations.
- Compare outputs when one input changes.
A cell reference such as B2 points to a value in a table. A formula can use cell references so the calculation updates when the values change.
What to write in your book
- Cell reference: a label like B2 that points to a spreadsheet value — the equivalent of a variable in algebra.
- Spreadsheet formula: begins with =, uses cell references and operators (*, +, -, /).
- Example: =B2*C2+D2 is the spreadsheet version of $T = qc + f$.
- Copying a formula down a column adjusts references automatically: =B2*C2 becomes =B3*C3 in the next row.
Did you get this? True or false: when a spreadsheet formula is copied from row 2 to row 3, it still uses B2 and C2 as its references.
A spreadsheet formula is only useful if the input values use consistent units. Mixing cents and dollars, minutes and hours, or single items and boxes can produce incorrect outputs.
The same principle applies in reverse: if hours worked are in column B but one row uses minutes, the formula =B2*C2 will multiply an incompatible value by the hourly rate.
What to write in your book
- Before using a spreadsheet formula, confirm that all input values share the same unit.
- Common unit errors: dollars vs cents, hours vs minutes, metres vs centimetres, individual items vs boxes.
- When changing inputs, only the relevant cell needs updating — the formula recalculates automatically.
- Check: if one input changes by a known amount, can you predict the output change without recalculating everything?
Quick check: A spreadsheet formula is =B2*C2+D2. B2 = 12, C2 = 4, D2 = 10. What is the output?
Worked examples · 3 in a row, reveal as you go
A spreadsheet uses =B2*C2+D2, where B2 is quantity, C2 is cost per item and D2 is a delivery fee. If B2 = 12, C2 = 4 and D2 = 10, find the order total.
A sports store records number of balls in column B and price per ball in column C. The total price for each row is in column D. Write the spreadsheet formula for row 2, then explain what happens when the formula is copied to row 3.
A formula is $T = 8n + 15$, where $n$ is the number of items. What happens when $n$ changes from 10 to 14?
What to write in your book
- Worked Example 1: =B2*C2+D2 → $T = qc + f = 12 \times 4 + 10 = 58$.
- Worked Example 2: =B2*C2 copies to =B3*C3. Same rule, adjusted references.
- Worked Example 3: $T = 8n + 15$. When $n$ increases by 4, $T$ increases by $4 \times 8 = 32$. Use the rate to shortcut recalculation.
- Key connection: algebra formula and spreadsheet formula are the same mathematical idea in different notations.
Fill the gap: A formula =B2*C2+E2 where B2 is hours, C2 is hourly rate and E2 is a booking fee means: Total $=$ $\times$ hourly rate $+$ . The algebra version is $T = $ .
Common errors · the 3 traps that cost marks
Quick-fire practice · 4 calculations
Write in words what =B2*C2+E2 means if B2 is hours, C2 is hourly rate and E2 is a booking fee.
Calculate the output when B2 = 5, C2 = 22 and E2 = 30 using the formula =B2*C2+E2.
Write a spreadsheet formula for total material cost if B2 is metres used and C2 is cost per metre.
Explain one error that could occur if some rows use metres and others use centimetres in the same column.
Match it: Which algebra formula matches the spreadsheet formula =B2*C2+D2, where B2 = quantity, C2 = price per item, D2 = delivery fee?
If quantity and price change row by row, a copied formula can calculate each order total. A delivery fee may be fixed or may also change, depending on the spreadsheet design.
Pick your answer, then rate your confidence — that tells the system what to drill next. Each retry pulls a fresh mix from the bank.
Q1. A spreadsheet formula is =B2*C2+D2. B2 is quantity, C2 is price per item and D2 is delivery. Calculate the total when B2 = 18, C2 = 3 and D2 = 12. (3 marks)
Q2. Write a spreadsheet formula for total pay if B2 is hours worked, C2 is hourly rate and D2 is a fixed allowance. (2 marks)
Q3. Explain why unit consistency matters when copying spreadsheet formulas down a table. (2 marks)
📖 Comprehensive answers (click to reveal)
Drill 1: Total = hours × hourly rate + booking fee · 2: Total $= 5 \times 22 + 30 = 110 + 30 = 140$ · 3: =B2*C2 · 4: Some rows would use a value in metres while others use a value 100× larger in centimetres; the formula would multiply incompatible values, producing incorrect totals for the centimetre rows.
Q1 (3 marks): Formula translation: Total = quantity × price per item + delivery [1]. Substitution: $18 \times 3 + 12$ [1]. Answer: $54 + 12 = \$66$ [1].
Q2 (2 marks): Spreadsheet formula: =B2*C2+D2 [1]. Algebra version: $T = hc + f$ where $T$ = total pay, $h$ = hours worked, $c$ = hourly rate, $f$ = fixed allowance [1].
Q3 (2 marks): If units are inconsistent (e.g. some rows use dollars and one uses cents), the formula multiplies a value that is 100 times larger or smaller than intended [1], producing incorrect outputs that may not be obviously wrong at a glance — errors can go undetected [1].
Translate each cell formula into words, then identify which input changes the output. Beat the boss to bank a tier — gold (90% + speed), silver (75%), or bronze (50%).
⚔ Enter the arenaClimb platforms by answering spreadsheet formula questions. Pool: lesson 6.
Mark lesson as complete
Tick when you've finished the practice and review.