Lesson 7 of 7 · 16 min

Two-way tables for two-variable formulas

Build and read a two-by-two (two-way) table of values for a formula with two variable quantities, such as BMI or a hire charge, using mixed cell references in a spreadsheet.

What you will be able to do

  • Lay out a two-way table with one variable down the first column and the other across the first row.
  • Read a value from a two-way table and find the inputs that produced it.
  • Write one spreadsheet formula, with mixed references such as $A2 and B$1, that fills both across and down.

The car hire formula C=65d+0.28kC = 65d + 0.28k has two variable quantities: the number of days, dd, and the kilometres driven, kk. A single column of inputs cannot show every combination. Instead, put one variable down the side and the other across the top. Each cell is the value of the formula for its row and its column. This is a two-way table, which the syllabus calls a two-by-two table.

Car hire cost in dollars, C=65d+0.28kC = 65d + 0.28k. For 3 days and 200 km, read across the 3-day row to the 200 km column: 65(3)+0.28(200)=195+56=$25165(3) + 0.28(200) = 195 + 56 = \$251.
Days ↓, km →100200300400
193121149177
2158186214242
3223251279307
4288316344372

The table does not have a column for 250 km. Use C=65d+0.28kC = 65d + 0.28k to calculate the cost of hiring the car for 3 days and driving 250 km.

Round to 2 decimal places.

In a spreadsheet, a two-way table needs one formula that works in every cell. When you fill it across, it must keep reading the row variable from column A. When you fill it down, it must keep reading the column variable from row 1. Mixed references do this:

  • $A2 locks the column (always column A) but lets the row change.
  • B$1 locks the row (always row 1) but lets the column change.

A BMI table. Heights (m) run down column A and masses (kg) across row 1. The one formula in B2, filled across and down, fills the whole table.

Which formula, entered in B2 of the BMI table, gives the correct BMI in every cell when filled across to column E and down to row 5?

Choose one answer

Worked example

A two-way BMI table in a spreadsheet

  • Complex familiar
  • Technology active

A school nurse builds the two-way BMI table shown in the lesson, with heights 1.6, 1.7, 1.8 and 1.9 m in A2 to A5 and masses 60, 70, 80 and 90 kg in B1 to E1.

(a) What does the formula in D4 calculate, and what value does it show, to one decimal place?

(b) How many cells in the table are in the healthy-weight range, 18.5 to under 25?

3 steps

Try it yourself first, then check the working one step at a time.

Method

  1. Track how mixed references change as a formula is filled across and down.

  2. Each cell uses its own row value and column value.

  3. Check borderline values unrounded before classifying them.

Watch out for

  • Reading the row and column the wrong way round: D4 means column D (80 kg) and row 4 (1.8 m).

In the BMI spreadsheet above, which cell shows the BMI of a person who is 1.7 m tall and has a mass of 80 kg? Give the cell reference, such as B2.

Common mistake

Two-way table slips

  • Locking the wrong part: the $ goes before the part that must not change. $A2 keeps column A; B$1 keeps row 1.
  • Mixing up rows and columns when reading: find the row value down the side and the column value across the top, then meet in the middle.
  • Units: in the BMI table the heights are in metres. A height column in centimetres needs /100 in the formula.

Tip

Without a spreadsheet

A scientific calculator does the same job more slowly: substitute each row and column pair. A spreadsheet earns its keep when the table is large or when you want to change the inputs and watch every cell update.

Summary

  • A two-way table shows a formula with two variable quantities: one down the side, one across the top.

  • Each cell is one substitution using its row value and its column value.

  • Mixed references fill a whole table from one formula: $A2 locks the column, B$1 locks the row.

  • Read a cell by finding the row and the column, and check one value by substituting by hand.

Finished the lesson?

Try the 3 quick checks above and the lesson completes itself, or mark it complete now.

Lesson 7 of 70% read

Save your progress with a free account