Week 16 · 2.1 Spreadsheets and analysing data · 3 lessons
1
Ranges, totals and extremes
Computing 9: Topic 2.1, pp. 168–197
GoalUse a cell range with SUM, MIN and MAX to total a row or a column and to find the largest and smallest values.
New words
cell range · a group of cells, written as the first cell, a colon and the last cell, such as B2:B6SUM · a function that adds up all the numbers in the cells it is givenMIN · a function that finds the smallest number in the cells it is givenMAX · a function that finds the largest number in the cells it is given
Explanation
Every cell has an address made of its column letter and row number, such as C4. A cell range names a whole group of cells with the first cell, a colon and the last cell. The colon means ‘to’, so B2:B6 is B2, B3, B4, B5 and B6. A range can be a column, a row or a block. For example, B2:F2 is a row, and B2:C4 is a block of six cells. SUM adds the numbers in the cells it is given. MIN gives the smallest and MAX gives the largest. You can list single cells, as in =SUM(B2,B3,B4), but a range is shorter, quicker to type and less likely to miss a cell. Text and empty cells in a range are ignored by these functions.
Range
Shape
Cells it contains
B2:B4
column
B2, B3, B4
B2:F2
row
B2, C2, D2, E2, F2
B2:C4
block
B2, C2, B3, C3, B4, C4
Worked examples
G2fx=SUM(B2:F2)
A
B
C
D
E
F
G
1
Rider
Mon
Tue
Wed
Thu
Fri
Total
2
Ravi
12
0
15
9
20
56
3
Lola
8
10
0
14
11
43
4
Kenji
5
7
9
6
13
40
Row totals. Three riders in a cycling club record the kilometres they rode each day. In G2 the formula =SUM(B2:F2) adds the five days for Ravi: 12 + 0 + 15 + 9 + 20 = 56. It was filled down, so G3 adds B3:F3 and G4 adds B4:F4.
G5fx=MAX(B2:F4)
A
B
C
D
E
F
G
1
Rider
Mon
Tue
Wed
Thu
Fri
Total
2
Ravi
12
0
15
9
20
56
3
Lola
8
10
0
14
11
43
4
Kenji
5
7
9
6
13
40
5
Longest day
20
6
Shortest day
0
Largest and smallest. A range can be a block of many rows and columns. =MAX(B2:F4) looks at all fifteen day figures and gives 20. =MIN(B2:F4) gives 0, a day on which a rider did not ride. These two answers are in column G, below the totals.
Formula
Cells added
Result
=SUM(B2:B4)
B2, B3, B4
25
=SUM(B2,B4)
B2, B4
17
=SUM(B2,B3,B4)
B2, B3, B4
25
A range or single cells? In the Monday column the riders rode 12, 8 and 5 km. =SUM(B2:B4) gives 25. A learner who typed =SUM(B2,B4) forgot the middle cell, so the answer is 12 + 5 = 17. A range makes it hard to miss a cell in the middle.
Class activity
Range hunt (no computer). Draw a spreadsheet grid of 5 columns and 6 rows on squared paper, label the columns A to E and the rows 1 to 6, and fill the cells with small numbers. Take turns to choose a range, for example B2:D4. Your partner shades it, lists its cells, and works out its sum, its largest and its smallest. Then swap: give the range as a shaded picture, and your partner writes it in range form.
Practice
1
Pages read by five children are in B2:B6. Write the formulas for B7 (the total), B8 (the largest) and B9 (the smallest), and work out their values.
A
B
1
Child
Pages
2
Ali
34
3
Mia
28
4
Leo
41
5
Nodira
19
6
Omar
36
7
Total
?
8
Most
?
9
Fewest
?
B7 =SUM(B2:B6) gives 158. B8 =MAX(B2:B6) gives 41. B9 =MIN(B2:B6) gives 19. The total is 34 + 28 + 41 + 19 + 36 = 158.
2
Name the highlighted range, then work out the sum, the largest and the smallest of its numbers.
A
B
C
D
1
Day
Cakes
Tarts
Pies
2
Sat
14
9
6
3
Sun
11
12
8
4
Mon
7
5
10
The range is B2:C3. Its cells are 14, 9, 11 and 12. The sum is 46, the largest is 14 and the smallest is 9.
3
The total shown in B7 is 110, but the five amounts really add up to 135. Find the fault and write the repaired formula.
B7fx=SUM(B2:B5)
A
B
1
Week
Money ($)
2
1
20
3
2
35
4
3
15
5
4
40
6
5
25
7
Total
110
The formula =SUM(B2:B5) stops one cell too early and leaves out B6 (25). 110 + 25 = 135. The repaired formula is =SUM(B2:B6).
4
Give two reasons why =SUM(B2:B50) is better than =B2+B3+B4+ … written out to B50.
It is much shorter, so it is quicker to type and to read. It is also less likely to contain a mistake such as a missing cell or a wrong address, and it is easier to change.