Stretch tasks Absolute and relative references

Round 1: Quiz Blitz

1. =B3*C3 Both references are relative, so both move down one row.
2. =B5*$E$1 B2 is relative and moves to B5. $E$1 is absolute, so it never changes.
3. False Dollar signs lock a reference so it does not change when copied. They have nothing to do with currency.
4. =B2*$E$1 Each row needs its own price, so B2 stays relative, but every row needs the same rate, so E1 is locked.
5. =B4*E3 Down two rows, so every relative row number goes up by two: B2 to B4 and E1 to E3. E3 is probably empty.
6. $B2 The dollar sign sits in front of whatever it locks. In $B2 it is in front of the column letter.
7. =B1+$B$1 Moving one column right changes A1 to B1. $B$1 is locked and stays the same.
8. A reference that should be absolute is relative It is the classic symptom: a reference that should have stayed put has drifted down with the copy.
9. $C$3; C$3 Copying down changes row numbers. A $ in front of the 3 locks the row, so both $C$3 and C$3 stay the same.
10. True Each row's total should use that row's own numbers, which is exactly what relative references do.
11. =A2*$F$1 The price must move down row by row, and the rate must not.
12. B2 → B$2 → $B$2 B2 moves freely, B$2 has its row locked, and $B$2 is fully locked.

Round 2: Quick Fire

1. • A relative reference changes when the formula is copied
   • An absolute reference is locked, so it stays the same
   Model answer

   A relative reference changes when the formula is copied, so B2 becomes B3 one row down. An absolute reference is locked with dollar signs, so $B$2 stays the same wherever it is copied.

2. • If the rate changes, it is changed once and every formula updates
   • The absolute reference keeps every copied formula pointing at that cell
   Model answer

   If the VAT rate changes, it only has to be changed once, in one place, and every formula updates. The absolute reference keeps every copied formula pointing at that same cell.

3. • =C3*D2
   • D2 is the wrong cell, so the answer is wrong; D1 should have been made absolute
   Model answer

   It becomes =C3*D2. D2 is the wrong cell, probably empty, so the answer is wrong. D1 should have been locked as an absolute reference, $D$1.

4. • When the row must stay fixed but the column can change
   • For example, when filling a grid such as a times table across and down
   Model answer

   When the row must stay fixed but the column should change, for example when one formula is filled across and down to make a grid like a times table, and every cell must use a number from row 2.

Round 3: Big Think

1. • E1 is relative, so it moves to E2, E3 and so on as the formula is copied
   • Those cells are empty, so the answer is 0
   • Fix it by making E1 absolute: $E$1
   • Then each row uses its own price but always the rate in E1
   Model answer

   E1 is a relative reference, so when the formula is filled down it moves to E2, E3 and so on. Those cells are empty, so every row after the first multiplies by zero and shows 0. The fix is to make the rate absolute, =B2*$E$1, and fill it down again: each row then uses its own price, B3, B4 and so on, but always the rate in E1.

2. • The formula multiplies a column A number by a row 1 number
   • A formula such as =$A2*B$1
   • $A locks the column, so it always uses the numbers down column A
   • $1 locks the row, so it always uses the numbers along row 1
   • The unlocked part changes, so each cell uses its own row and column
   • These are mixed references
   • Without the dollar signs the references drift and give wrong answers
   Model answer

   Each cell must multiply the number in column A on its row by the number in row 1 above its column. The formula =$A2*B$1 in B2 does this using mixed references. The $ in front of A locks the column, so wherever it is copied it always uses the numbers down column A, while the row part changes as it is filled down. The $ in front of 1 locks the row, so it always uses the numbers along row 1, while the column part changes as it is filled across. Without the dollar signs both references would drift as the formula is copied and the grid would be full of wrong answers.

This or That

1. =C2*D2 Copying right moves each column letter on by one. The row numbers stay the same.
2. $B$2 $B$2 locks both the column and the row. B$2 locks only the row.
3. =B$2 The dollar before the 2 locks the row, so copying down changes nothing.
4. =$B3 Only the column is locked, so the row number still moves.
5. The one pointing at H1 Every row must use the same H1, so lock it as $H$1. The price reference should move row by row.
6. Relative With no dollar signs, B2 changes when the formula is copied.
7. $B2 A mixed reference locks only one part. $B2 locks the column but not the row.
8. Lock the reference that should not move The missing dollar signs are the fault. Retyping every row wastes time and invites typing mistakes.

Memory Match

• A 2 B2: Column and row both move when copied
• B 6 $B$2: Column and row both locked
• C 4 $B2: Column locked, row moves
• D 5 B$2: Row locked, column moves
• E 3 Absolute reference: Points at one fixed cell, such as a VAT rate
• F 1 Fill down: Copying a formula into the cells below it