Lesson 7: Two-way tables for two-variable formulas
A two-way BMI table in a spreadsheet
SkillsTwo-way tables of valuesSpreadsheet formulas
Worked example
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.
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.
Try a similar one
A fresh question of the same kind, checked as you answer, with hints and a full solution.
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).