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 has two variable quantities: the number of days, , and the kilometres driven, . 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.
| Days ↓, km → | 100 | 200 | 300 | 400 |
|---|---|---|---|---|
| 1 | 93 | 121 | 149 | 177 |
| 2 | 158 | 186 | 214 | 242 |
| 3 | 223 | 251 | 279 | 307 |
| 4 | 288 | 316 | 344 | 372 |
The table does not have a column for 250 km. Use to calculate the cost of hiring the car for 3 days and driving 250 km.
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:
$A2locks the column (always column A) but lets the row change.B$1locks 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?
Worked example
A two-way BMI table in a spreadsheet
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?
Step 1: Find the formula in D4
B2 holds
=B$1/$A2^2. Moving two columns right and two rows down, it becomes=D$1/$A4^2.B$1 keeps the row and lets the column change to D; $A2 keeps the column and lets the row change to 4.
Step 2: Evaluate it
D1 is 80 kg and A4 is 1.8 m, so .
Step 3: Count the healthy cells row by row
- 1.6 m: 23.4 → 1 cell
- 1.7 m: 20.8, 24.2 → 2 cells
- 1.8 m: 18.5, 21.6, 24.7 → 3 cells
- 1.9 m: 19.4, 22.2, 24.9 → 3 cells (16.6 is underweight)
Check the borderline cells with the unrounded values: is at least 18.5, and is under 25.
Try it yourself first, then check the working one step at a time.
Answer
(a) D4 holds =D$1/$A4^2, the BMI of an 80 kg person 1.8 m tall: about 24.7. (b) 9 cells are in the healthy range.
Method
Track how mixed references change as a formula is filled across and down.
Each cell uses its own row value and column value.
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
/100in 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.