Formula Modelling with Spreadsheets

Connect algebra formulas to spreadsheet-style calculations by identifying inputs, fixed values, variable values and repeated formulas.

45 min Algebra Formulas and equations Lesson 6 of 13
[]

Choose how you work: type answers on screen, or work in your book.

Printable worksheet

Open this lesson's worksheet

Use the printable version for cell-style formulas, repeated calculations and interpreting changed inputs.

Open printable version
Think First

A school canteen spreadsheet uses quantity, price per item and a fixed delivery fee to calculate order totals. What numbers should change from row to row, and what might stay fixed?

Type the values that should change and stay fixed.

Write the values that should change and stay fixed in your book.

Write your response in your book
Saved

Know

  • 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.

Understand

  • 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.

Can Do

  • Describe a spreadsheet formula in words.
  • Write a cell-style formula for repeated calculations.
  • Compare outputs when one input changes.
=

Spreadsheet Formula Structure

total = fixed cost + quantity x unit cost
Spreadsheet version: =B2*C2+D2
$T = qc + f$
$T$ = total, $q$ = quantity, $c$ = cost per item, $f$ = fixed cost

Core Content

1. Cell References Stand In for Values

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.

CellMeaningValue
B2Quantity12
C2Cost per item$4
D2Delivery fee$10

The formula =B2*C2+D2 means total equals quantity times cost per item, plus delivery fee.

Worked Example 1

Interpret a spreadsheet formula

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:

Total $= 12 \times 4 + 10$

Total $= 48 + 10 = 58$

Answer: The order total is $58.

Worked Example 2

Write a repeated row formula

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.

For row 2, the total is quantity times price per ball.

Spreadsheet formula: =B2*C2

Algebra version: $T = qp$

When copied to row 3, the same idea becomes =B3*C3.

Worked Example 3

Compare outputs when one input changes

A formula is $T = 8n + 15$, where $n$ is the number of items. What happens when $n$ changes from 10 to 14?

For $n = 10$: $T = 8(10) + 15 = 95$.

For $n = 14$: $T = 8(14) + 15 = 127$.

The output increases by $32 because 4 extra items each add $8.

Reasoning habit: You do not need to recalculate everything if you can identify which input changed and by how much.

2. Keep Units and Inputs Consistent

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.

Common error: If C2 is a cost in dollars, do not enter one row in cents unless the formula is designed for cents.
Activity

Spreadsheet Formula Practice

  1. Write in words what =B2*C2+E2 means if B2 is hours, C2 is hourly rate and E2 is a booking fee.
  2. Calculate the output when B2 = 5, C2 = 22 and E2 = 30.
  3. Write a spreadsheet formula for total material cost if B2 is metres used and C2 is cost per metre.
  4. Explain one error that could occur if some rows use metres and others use centimetres.
Complete the spreadsheet practice in your book.

Revisit the Canteen Spreadsheet

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.

Explain the connection in your book.

Assessment

MC

Multiple Choice

Random questions from the lesson bank - feedback appears immediately.

SA

Short Answer

Interpret spreadsheet formulas and explain input changes.

1. 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

Answer in your book.

2. Write a spreadsheet formula for total pay if B2 is hours worked, C2 is hourly rate and D2 is a fixed allowance. 2 MARKS

Answer in your book.

3. Explain why unit consistency matters when copying spreadsheet formulas down a table. 2 MARKS

Answer in your book.

Cell Reference Sprint

Translate each cell formula into words, then identify which input changes the output.

Back to module