092i - Exploring Formulas with Spreadsheets

Lesson 92 — Using Digital Tools to Explore Formulas

Strand: Algebra | Descriptor: AC9M7A06 | Duration: 45 minutes

Room/equipment: devices with spreadsheet software (as Lesson 80).

Learning Intentions

• To implement multi-variable formulas in a spreadsheet.
• To generate whole families of results by filling a formula down a column.

Success Criteria

I can:

1. Translate an algebraic formula into a spreadsheet formula with cell references.
2. Fill a formula down to evaluate it for many inputs at once.
3. Use absolute references ($) to hold one variable fixed while another varies.
4. Read patterns out of the generated table.

Warmup

(6 minutes — translation drill, mini whiteboards)

Write each as a spreadsheet formula, with the variables in cells A2, B2, C2 as needed:

5. What is wrong with typing 2A2 + 2B2?

Answers: 1. =A2*B2*C2; 2. =2*A2+2*B2; 3. =(A2+B2)*C2/2; 4. =4.5+2.2*A2; 5. Spreadsheets need every * written — juxtaposition (algebra’s shorthand) does not exist there.

The point: the spreadsheet is stricter than algebra — brackets and stars everywhere. That strictness is useful: it forces the structure into the open.

Activities

Activity 1 — Guided Build: One Formula, Many Inputs (14 min)

Teacher-led build; students follow. The taxi formula from Lesson 91: .

Stage 1 — single row:

	A	B	C
1	km	wait (min)	fare
2	10	5	=4.5+2.2*A2+0.8*B2

Verify against Lesson 91’s by-hand answer: 30.50$ ✓ (the by-hand check is the habit — Lesson 80’s rule).

Stage 2 — a column of journeys. Enter kilometres – down column A (fill handle from ), hold waiting at , fill the fare formula down.

Read the table:

1. What does the fare column step by? (2.20$ — the coefficient, visible as the constant difference. Lessons 87–88’s step, now generated by machine.)
2. Which journey first costs over 30k = 10$30.50$.)*
3. Predict the fare at without filling further; then fill and check. (52.50$.)

Stage 3 — the $ lock. Move the waiting time to a single cell E1 so one edit changes every fare: formula becomes =4.5+2.2*A2+0.8*$E$1, filled down.

Why the dollars: without them, filling down drags E1 to E2, E3, … — empty cells, wrong fares. $E$1 pins the reference. Demonstrate the breakage first, then fix it — the error teaches the syntax.

Now change E1 from to : every fare updates. One fact, typed once (Lesson 80’s principle, now with a parameter).

Activity 2 — Paired Build: the Two-way Table (14 min)

Pairs. The lesson’s centrepiece: vary two variables at once.

Build a fare grid: kilometres down column A (rows 2–6); waiting minutes across row 1 (columns B–E). In B2 enter one formula that can fill the whole grid:

=4.5+2.2*$A2+0.8*B$1

Fill right, then down.

The mixed locks, explained at the board: $A2 pins the column (kilometres always come from A) but lets the row slide; B$1 pins the row (waiting always from row 1) but lets the column slide. One formula, twenty fares.

Interrogate the grid:

1. Read the fare for km with min waiting. (25.70$.)
2. Moving one cell right adds how much? One cell down? (4$4.40$ — two kilometres.)
3. Find two different journeys with (almost) the same fare. (E.g. km/ min 22.1065= $21.70$ — near-misses count; discuss exactness.)
4. Shade all fares under 2012.75$ min.)*

Socratic prompts while circulating:

Prompt	Purpose
Delete your grid and refill from B2 — does it survive?	Tests the locks are right, not luck.
What would $A$2 (both locked) do to the grid?	Every cell would use km — one wrong lock, twenty wrong fares.
Where in the grid does each coefficient live?	is the down-step per km-row; the right-step — structure made spatial.

Activity 3 — Independent Build: Formula of Choice (11 min)

Individually. Choose one formula from Lesson 91’s circuit and build a two-way table for it.

Suggested targets:

• with fixed in a parameter cell: length down, width across.
• with as the parameter: rate across (–), years down (–).
• with parameterised.

Deliverable: the grid, plus two written observations of the form “moving [direction] changes the output by [amount] because [coefficient/variable]“.

Early-finisher extension: for the interest grid — find where the interest first exceeds half the principal, and describe the boundary’s shape. (With : , a hyperbola-ish staircase — high rate pairs with few years and vice versa.)

Checks for Understanding

(5 minutes — exit ticket, on paper)

1. Write as a spreadsheet formula for values in A2, B2, C2.
2. What does the $ in $E$1 do when the formula is filled down?
3. In a fill-right/fill-down grid formula, why is the reference $A2 and not A2 or $A$2?
4. A fare column steps by 1.801$. What is the per-km rate in the formula?
5. Reasoning. Why is a two-way table better than twenty separate calculations?

Answers: 1. =A2*B2*C2; 2. Pins the reference to E1 — it does not slide as the formula moves; 3. The column must stay A (kilometres live there) while the row slides with the fill; both locked would freeze one input for the whole grid; 4. 1.80$/km — the step is the coefficient; 5. One formula generates all cases, updates when parameters change, and the pattern across cases (steps, boundaries) becomes visible — single calculations show none of that.

Common Misconceptions

Misconception	How to pre-empt it
Omitting * (typing 2A2).	The warmup’s Q5; the error message becomes a teaching moment.
No $ on the parameter, dragging into empty cells.	Demonstrate the breakage deliberately before fixing it.
Both-locked $A$2 in the grid formula.	The circulating prompt; twenty identical rows expose it.
Typing each fare by hand into the grid.	The audit question: change E1 — does your grid update?
Reading the grid without units.	Fares are dollars; label the corner cell.
Believing the spreadsheet found the pattern.	The steps were put there by the coefficients; the sheet only reveals them. Connect back to Lesson 91’s exchange-rate reading.

Enrichment — Competition-Style Problems

E1 (Kangaroo style). A grid formula =$A2*B$1 fills a block where column A holds – and row 1 holds –. What table results?

Answer

The multiplication table up to .

E2 (AMC Junior style). In the taxi grid, which journeys cost exactly 26.504.5 + 2.2k + 0.8m = 26.5k, m$.

Answer

, i.e. : must make divisible by — ; ; .

E3 (Challenge). An interest grid uses =$G$1*A2*B$1 with principal in G1, years down column A, rates across row 1. The cell for years at shows . Find the principal.

Answer

350$.

E4 (Challenge). A student’s grid shows the same value in the cells (row , col ) and (row , col ) — and this symmetry holds everywhere. What must be true of the formula?

Answer

Swapping the two inputs leaves the output unchanged — the formula is symmetric in them, e.g. a product =$A2*B$1 or sum. The taxi grid is not symmetric (), which is exactly why its rows and columns step differently.

Homework

(Paper-based spreadsheet reasoning, as Lesson 80.)

1. Write as spreadsheet formulas (inputs in A2, B2, C2): (a) (b) (c) (d) .
2. A column contains kilometres and the fare formula =3+2.4*A2 filled down. (a) The first three fares. (b) The step between fares. (c) The first fare over 27$.
3. A grid uses =2*($A2+B$1) with lengths down column A and widths across row 1. (a) What formula is this? (b) Write out the nine grid values. (c) Which cells show the value ?
4. Explain the difference between E1, $E1, E$1 and $E$1 when a formula is filled both right and down.
5. A two-way table of (height fixed at ) shows the value somewhere. List all whole-number cells that could show it.
6. Reasoning. A grid’s rows step by and its columns step by . What can you conclude about the formula’s coefficients?
7. Challenge. Design on paper a two-way table for the trapezium area with fixed: – down, – across. Fill in all values and describe the two symmetries you find.

Answers: Q1 — (a) =4*A2 (b) =A2*B2/2 (c) =35+18*A2 (d) =(A2+B2+C2)/3. Q2 — (a) (b) (c) at 29.40k = 10$278,10,1210,12,1412,14,16(l,w) = (3,3), (4,2), (5,1)E1locks column only;EElw = 12(1,12),(2,6),(3,4),(4,3),(6,2),(12,1)732(a+b)a,ba + ba+b$ enters the formula.