Count, total, decide
Answers are for parents and teachers.
1
Four classes recorded the points they earned in a reading challenge. Write the formulas for the total, the highest and the lowest of the points in B2:B5, and for the number of classes with more than 20 points. The points are 18, 25, 31 and 12.
| A | B | |
|---|---|---|
| 1 | Class | Points |
| 2 | A | 18 |
| 3 | B | 25 |
| 4 | C | 31 |
| 5 | D | 12 |
Total =SUM(B2:B5) is 86. Highest =MAX(B2:B5) is 31. Lowest =MIN(B2:B5) is 12. Number with more than 20: =COUNTIF(B2:B5,">20") is 2, for 25 and 31.
2
A column has these seven cells: 6, 9, the word none, an empty cell, 14, 3, 9. Work out COUNT, COUNTA, COUNTIF with "=9" (or 9) and MAX for the seven cells.
COUNT: 5, the numbers 6, 9, 14, 3 and 9. COUNTA: 6, because the empty cell is not counted but the word none is. COUNTIF for 9: 2. MAX: 14.
3
Write the range that covers the cells that are highlighted, and work out the sum of its numbers.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Q1 | Q2 | Q3 |
| 2 | a | 1 | 5 | 8 |
| 3 | b | 4 | 3 | 6 |
| 4 | c | 9 | 7 | 2 |
The range is C2:D4. Its numbers are 5, 8, 3, 6, 7 and 2, which add up to 31.
4
A formula in E2, =D2*B10, multiplies a sales figure by a rate in B10. It is filled down to E4, and only the first answer is right. Explain what has gone wrong and repair the formula.
When filled, the reference B10 becomes B11 and B12, which are empty cells and count as 0. The rate must be fixed with $: =D2*$B$10.
5
A model of a bake sale totals the sales and shows the best-selling item with MAX. Write two conclusions you could draw from the figures, and one limitation of the model. Invent suitable figures first.
Answers vary. A good answer states figures, for example ‘Cookies sold the most, 48, and the total was 210’, then a fair conclusion, such as ‘the stall should bake more cookies’, and a limitation, such as ‘the data covers only one day, so the pattern may not hold’.