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
3

Locked cells and colour rules

Computing 7: Unit 7.4, pp. 112–149
GoalExplain relative and absolute cell references, and write a conditional formatting rule that highlights important data.
New words
relative reference · a cell reference that changes when the formula is copied to another cellabsolute reference · a cell reference with $ signs, such as $B$1, that stays the same when copiedfill handle · the small square at the corner of a cell that you drag to copy its formulaconditional formatting · a rule that changes how a cell looks only when its value meets a condition
Explanation

When you copy a formula down a column, each cell reference moves down by the same number of rows. This is a relative reference, and it is usually helpful, because each row uses its own numbers. But sometimes one value must stay in the same place, such as a single price. Putting $ before the column letter and before the row number locks the cell: $B$1 is an absolute reference and never changes. In a spreadsheet you drag the fill handle to copy a formula. Conditional formatting is a rule with three parts: the cells, a condition such as = 0 or > 40, and a format such as a green fill. The format is shown only when the condition is true. Change a number in the model and the colours change, which helps people see quickly what is happening.

Formula in C3Copied to C4 it becomesKind of reference
=B3*2=B4*2relative: moves down
=$B$1*B3=$B$1*B4$B$1 is absolute, B3 is relative
Worked examples
C4fx=B2*B4
ABC
1Ticket price4
2ClassTicketsIncome
33A1248
43B9#VALUE!
53C15180
63D763
The school play sells tickets at the price in B1. Ravi typed =B1*B3 in C3 and copied it down. The relative reference B1 moved down too, so C4 now points to B2, which is a label, and C5 uses the wrong numbers. 12 × 15 = 180 is not the income of class 3C.
C4fx=$B$1*B4
ABC
1Ticket price4
2ClassTicketsIncome
33A1248
43B936
53C1560
63D728
The fix is to lock the price: =$B$1*B3. Now every row uses B1, and only the number of tickets moves down. All four incomes are correct: 4 × 12 = 48, 4 × 9 = 36, 4 × 15 = 60 and 4 × 7 = 28.
ABC
1Ticket price4
2ClassTicketsIncome
33A1248
43B936
53C1560
63D728
A teacher sets this rule: cells C3:C6, condition ‘greater than 40’, format green fill. Only the incomes above 40 are shaded: 48 and 60. The values 36 and 28 are not above 40, so they stay plain. If the ticket price is changed from 4 to 5, the incomes become 60, 45, 75 and 35, and the shading moves to three cells.
Class activity

Colour rule game (no computer). Draw a table of ten numbers on paper, for example the temperature at noon on ten days. A partner calls a rule such as ‘less than 15’ or ‘equal to 20’ or ‘greater than 25’, and you colour only the cells that meet it. Check each other’s colouring. Then play ‘copy the formula’: write =B2*3 in C2 on paper, and then write by hand the formulas that would appear in C3, C4 and C5 after copying down. Finally write one with a locked cell, such as =$E$1*B2, and copy it down again. If you have a computer or tablet, an adult can help you try a colour rule.

Practice
1
The formula =B2*C2 is in D2 and is copied down. Write the formulas that appear in D3 and D5.
2
A stall sells cups of juice at the price in B1. Write the formula for C3 so that it can be copied to C4 and C5, and work out the three incomes.
ABC
1Price per cup3
2DayCupsIncome
3Mon20?
4Tue35?
5Wed15?
3
The rule is ‘cells B2:B6, less than 10, green fill’. Which cells turn green?
AB
1DayLitres
2Mon14
3Tue8
4Wed10
5Thu3
6Fri12
4
Write a conditional formatting rule (cells, condition, format) that shows a red fill when a library book has fewer than 2 copies left, in cells D2:D9. Why is a colour rule better than checking every number by eye?