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
1

Labels, cells and formulas

Computing 7: Unit 7.4, pp. 112–149
GoalName any cell in a spreadsheet, tell labels from data, and write a formula that calculates from other cells.
New words
cell · a box in a spreadsheet where a row and a column meetcell reference · the name of a cell: the column letter and then the row number, such as C4label · a heading or word that explains the data next to itformula · a calculation that starts with = and uses numbers, cell references and symbols
Explanation

A spreadsheet is a grid of boxes called cells. The columns have letters and the rows have numbers. The name of a cell is its column letter followed by its row number, so C4 is in column C and row 4. This name is the cell reference. A cell can hold a label, a number or a formula. A label is text that explains the data, such as Price, and it is often made bold so that it stands out. A formula always starts with an = sign. It can use cell references and the symbols + (add), − (subtract), * (multiply) and / (divide). The cell shows the answer, and the formula appears in the formula bar.

D2fx=B2*C2
ABCD
1PlantPriceSoldIncome
2Basil21428
3Mint3927
4Tomato4624
5Pepper5420
Worked examples
Cell referenceWhat it holdsKind
B33number: the price of mint
C54number: how many peppers were sold
A4Tomatolabel
D1Incomelabel
Cell references are like map squares. This table reads the plant-sale sheet above: it says what is stored in some cells.
ABCD
1PlantPriceSoldIncome
2Basil22040
3Mint3927
4Tomato4624
5Pepper5420
A formula keeps the answer up to date. Here the club sold 20 baskets of basil instead of 14. Only C2 was changed, but the formula in D2 worked out the new income by itself: 2 × 20 = 40. A typed-in answer would have stayed at 28 and been wrong.
In mathsFormulaResult
3 × 9=B3*C327
9 ÷ 3=C3/B33
3 + 9=B3+C312
9 − 3=C3-B36
3 × 9 without =B3*C3shows the text B3*C3
The four calculation symbols look different in a spreadsheet. Use * for ‘times’ and / for ‘divided by’. Here B3 holds 3 and C3 holds 9. If you leave out the = sign, the spreadsheet treats your typing as plain text.
Class activity

Paper spreadsheet (no computer). On squared paper draw a grid with columns A to D and rows 1 to 5, and write the letters and numbers along the edges. Fill in a small table about your family’s fruit bowl: labels in row 1 (Fruit, Price, Number, Cost) and three fruits below. A partner calls out cell references such as ‘C3!’ and you point to the cell and read what it holds. Then write the formula for the first Cost cell, say which cells it uses, and work out the answer by hand. If you have a computer or tablet, an adult can help you type the same table into a spreadsheet and check your answers.

Practice
1
Look at the bakery sheet. Which cell holds the price of the cake, and which cell holds the number of rolls?
ABCD
1ItemPriceNumberCost
2Bread310
3Rolls125
4Cake84
2
In the bakery sheet, write the formula for D2 that works out the cost of the bread. What number does it show? Then write the formula for D4.
ABCD
1ItemPriceNumberCost
2Bread310?
3Rolls125?
4Cake84?
3
Three people typed a formula into D2 of the bakery sheet. Say whether each one works. If it does not, say what is wrong.
Typed in D2Does it work?
B2*C2?
=B2 x C2?
=B2*C2?
4
The baker sells 5 more rolls, so C3 changes from 25 to 30. Cost cell D3 holds the formula =B3*C3. What does D3 show now? What would happen if the baker had typed the answer 25 into D3 instead of a formula?