GoalUse cell ranges with the SUM and AVERAGE functions, and spot the common mistakes in a function.
New words
cell range · a group of cells named by the first and last cell, such as B2:B6function · a ready-made tool in a spreadsheet that does a job, such as addingSUM · the function that adds all the numbers in a rangeAVERAGE · the function that adds the numbers in a range and divides by how many there are
Explanation
Adding many cells with + is slow: =B2+B3+B4+B5+B6. A cell range is a faster way to name a group of cells. B2:B6 means every cell from B2 down to B6, because the colon means ‘from … to …’. A function is a ready-made tool. SUM adds all the numbers in a range, so =SUM(B2:B6) gives the total. AVERAGE adds them and divides by how many numbers there are. A range can also be a block: B2:C6 is two columns and five rows, which is ten cells. A function needs a = sign at the start, a correct name and brackets that open and close.
B7fx=SUM(B2:B6)
A
B
C
1
Day
Garden A
Garden B
2
Mon
12
8
3
Tue
15
10
4
Wed
9
7
5
Thu
14
11
6
Fri
10
9
7
Total
60
45
8
Average
12
9
Worked examples
Formula
Cells used
Result
=B2+B3+B4+B5+B6
five cells, one by one
60
=SUM(B2:B6)
the range B2:B6
60
=SUM(B2:C6)
the block B2:C6, ten cells
105
The first two formulas in the table add the water used in Garden A. The function is shorter and easier to check. The last formula uses a block of two columns, so it adds both gardens.
Formula
Working
Result
=SUM(B2:B6)/5
60 ÷ 5
12
=AVERAGE(B2:B6)
(12 + 15 + 9 + 14 + 10) ÷ 5
12
=AVERAGE(C2:C6)
(8 + 10 + 7 + 11 + 9) ÷ 5
9
The average of Garden A is the total divided by the number of days: 60 ÷ 5 = 12. =AVERAGE(B2:B6) does both steps at once. Average is a good way to compare a garden that was watered on five days with one that was watered on three.
C7fx=SUM(C2:C6)
A
B
C
1
Day
Garden A
Garden B
2
Mon
12
8
3
Tue
15
10
4
Wed
9
7
5
Thu
14
11
6
Fri
10
9
7
Total
60
45
8
Average
12
9
The total of Garden A was worked out in B7 by =SUM(B2:B6). When this formula is copied one column to the right, B becomes C and the new formula in C7 adds Garden B: 8 + 10 + 7 + 11 + 9 = 45.
Class activity
Be the spreadsheet (no computer). Each player writes five numbers (for example the number of steps they walked on five days, or pages read) down a column on paper. Another player writes a formula on a slip, such as =SUM(B2:B6) or =AVERAGE(B2:B6), and the ‘spreadsheet’ works out the answer by hand. Then the slip-writer makes one deliberate mistake, such as a missing bracket or a missing =, and the ‘spreadsheet’ must say ERROR and explain why. Swap roles after three rounds. If you have a computer or tablet, an adult can help you check your answers with the real functions.
Practice
1
This sheet shows weekly rainfall at a school. What do =SUM(B2:B6) and =AVERAGE(B2:B6) show in B7 and B8?
Each formula below is meant to add B2 to B6, but it has a problem. Say what is wrong with each one.
Formula typed
What is wrong?
=SUM(B2:B6
?
SUM(B2:B6)
?
=SUM(B2-B6)
?
=SUM(B2:B6)/5
?
The first has no closing bracket. The second has no = sign, so it is shown as text. The third uses a minus sign instead of a colon, so it works out B2 − B6 instead of adding the range. The fourth divides the total by 5 as well, so it gives an average, not a total.
4
In the library sheet the formula in B6 is =SUM(B2:B5). It is copied across to D6. What formula is in D6, and what does it show?
B6fx=SUM(B2:B5)
A
B
C
D
1
Day
Fiction
Comics
Maths
2
Mon
6
5
1
3
Tue
4
8
2
4
Wed
7
3
0
5
Thu
3
6
3
6
Total
20
22
?
D6 holds =SUM(D2:D5). It shows 0 + 1 + 2 + 3 = 6 for the Maths column.