Formulas that do the sums
Answers are for parents and teachers.
1
Draw a small grid on paper with columns A to E and rows 1 to 6. Write the cell reference of (a) the cell in the third column and the fourth row, (b) the cell in the last column and the first row.
(a) C4. (b) E1. The column letter always comes first, then the row number.
2
What does the formula in B7 show? Work it out by hand.
| A | B | |
|---|---|---|
| 1 | Month | Pages read |
| 2 | Jan | 6 |
| 3 | Feb | 4 |
| 4 | Mar | 9 |
| 5 | Apr | 1 |
| 6 | May | 5 |
| 7 | Total | ? |
=SUM(B2:B6) adds 6 + 4 + 9 + 1 + 5 = 25. B7 shows 25.
3
Zoe says: ‘I will type the total instead of using a formula, because it is quicker.’ Explain to her why a formula is better. Use the word ‘change’ in your answer.
A formula recalculates by itself when any number in its cells is changed. A typed total stays the same and becomes wrong after a change, and you would have to work it out again by hand.
4
A teacher’s sheet has a quiz mark in each of the cells B2 to B9 and the formula =B2/10*100 in C2 that is copied down to C9. Write the formula that appears in C5. Is this a relative or an absolute reference?
C5 holds =B5/10*100. The reference B2 is a relative reference, so it moves down three rows with the copy.
5
Write a conditional formatting rule (cells, condition and format) for a sheet of fruit stock in cells B2:B10, so that the shop owner sees at once which fruits are almost sold out (fewer than 5 left). Which numbers in 12, 5, 4, 0, 9 get the format?
Cells B2:B10, condition less than 5, format red fill. The numbers 4 and 0 get the format. The number 5 is not less than 5.