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

Formulas that do the sums

Week 17 · Unit 7.4: The power of data · 3 lessons
2

Totals and averages

Computing 7: Unit 7.4, pp. 112–149
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)
ABC
1DayGarden AGarden B
2Mon128
3Tue1510
4Wed97
5Thu1411
6Fri109
7Total6045
8Average129
Worked examples
FormulaCells usedResult
=B2+B3+B4+B5+B6five cells, one by one60
=SUM(B2:B6)the range B2:B660
=SUM(B2:C6)the block B2:C6, ten cells105
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.
FormulaWorkingResult
=SUM(B2:B6)/560 ÷ 512
=AVERAGE(B2:B6)(12 + 15 + 9 + 14 + 10) ÷ 512
=AVERAGE(C2:C6)(8 + 10 + 7 + 11 + 9) ÷ 59
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)
ABC
1DayGarden AGarden B
2Mon128
3Tue1510
4Wed97
5Thu1411
6Fri109
7Total6045
8Average129
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?
AB
1WeekRain (mm)
2Week 15
3Week 20
4Week 312
5Week 43
6Week 55
7Total?
8Average?
2
Write a formula for each job. (a) The total of cells D3 to D12. (b) The average of cells B4 to E4. (c) The sum of B1, B2 and B3 using only + signs.
3
Each formula below is meant to add B2 to B6, but it has a problem. Say what is wrong with each one.
Formula typedWhat is wrong?
=SUM(B2:B6?
SUM(B2:B6)?
=SUM(B2-B6)?
=SUM(B2:B6)/5?
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)
ABCD
1DayFictionComicsMaths
2Mon651
3Tue482
4Wed730
5Thu363
6Total2022?