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

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

1 of 3 steps
  1. 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.

Try a similar one

A fresh question of the same kind, checked as you answer, with hints and a full solution.

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

Save your progress with a free account