← Back to the activity

Year 9 Computing — Copying formulas, and references that stay put

Work out what a formula points at once it has been copied, and use a dollar sign to stop a reference moving.

Name: ________________________

  1. 1.Each of these formulas sits in C2 and is copied down into C3. Sort each one by whether the copy reads any differently from the original.

    Groups: The copy reads differently · The copy reads exactly the same

    • =A2*3
    • =$A$1*3
    • =A$2+B$2
    • =A2+B2
    • =$A$2+$B$2
    • =$A2+$B2

  2. 2.Cell A1 holds =B1+C1. The formula is copied down into A2. In the copy, which cell takes the place of B1?

  3. 3.Every one of these formulas sits in row 2 and is copied down into row 3. Match each formula to what the copy reads.

    • =A2
    • =$A2
    • =A2+B2
    • =A2*5
    • =SUM(A1:A2)
    • =A3+B3
    • =SUM(A2:A3)
    • =$A3
    • =A3
    • =A3*5

  4. 4.Cell A2 holds =A1+B1. The formula is copied into C5, so it has moved both across and down. In the copy, which cell takes the place of A1?

  5. 5.A times-table grid is being built. Column A holds the numbers going down and row 1 holds the numbers going across. One formula is written in B2 and then copied both across and down to fill the whole grid. Which formula belongs in B2?

    • a) =A$2*$B1
    • b) =A2*B1
    • c) =$A2*B$1
    • d) =$A$2*$B$1

  6. 6.Cell E2 holds =E1+5. The formula is copied across into F2. Which cell does the copy add 5 to?

  7. 7.Column B holds a price on every row. One single rate sits in F1. A formula is to be written in C2 and then copied all the way down the column, multiplying each price by that rate. Which formula belongs in C2?

    • a) =B2*$F$1
    • b) =B2*F1
    • c) =$B$2*$F$1
    • d) =$B$2*F1

  8. 8.Cell N3 holds =SUM(N1:N2). The formula is copied down into N5. In the copy, which cell is the last one in the range being added up?

Answer key — Year 9 Computing — Copying formulas, and references that stay put

  1. 1. =A2+B2 → The copy reads differently; =$A$2+$B$2 → The copy reads exactly the same; =A$2+B$2 → The copy reads exactly the same; =$A2+$B2 → The copy reads differently; =A2*3 → The copy reads differently; =$A$1*3 → The copy reads exactly the same
  2. 2. B2
  3. 3. =A2 → =A3; =$A2 → =$A3; =A2+B2 → =A3+B3; =A2*5 → =A3*5; =SUM(A1:A2) → =SUM(A2:A3)
  4. 4. C4
  5. 5. c) =$A2*B$1
  6. 6. F1
  7. 7. a) =B2*$F$1
  8. 8. N4