AdvertisementYour brand could be hereYour brand hereOn every Darslab page · students, parents and teachersinfo@darslab.comAsk for pricesPricesinfo@darslab.com
☰ Year plan · Computing

Count, total, decide

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.

RangeShapeCells it contains
B2:B4columnB2, B3, B4
B2:F2rowB2, C2, D2, E2, F2
B2:C4blockB2, C2, B3, C3, B4, C4
Worked examples
G2fx=SUM(B2:F2)
ABCDEFG
1RiderMonTueWedThuFriTotal
2Ravi1201592056
3Lola8100141143
4Kenji57961340
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)
ABCDEFG
1RiderMonTueWedThuFriTotal
2Ravi1201592056
3Lola8100141143
4Kenji57961340
5Longest day20
6Shortest day0
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.
FormulaCells addedResult
=SUM(B2:B4)B2, B3, B425
=SUM(B2,B4)B2, B417
=SUM(B2,B3,B4)B2, B3, B425
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.
AB
1ChildPages
2Ali34
3Mia28
4Leo41
5Nodira19
6Omar36
7Total?
8Most?
9Fewest?
2
Name the highlighted range, then work out the sum, the largest and the smallest of its numbers.
ABCD
1DayCakesTartsPies
2Sat1496
3Sun11128
4Mon7510
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)
AB
1WeekMoney ($)
2120
3235
4315
5440
6525
7Total110
4
Give two reasons why =SUM(B2:B50) is better than =B2+B3+B4+ … written out to B50.