Week 16 · 2.1 Spreadsheets and analysing data · 3 lessons
3
Fix it and decide
Computing 9: Topic 2.1, pp. 168–197
GoalFix one cell in a formula with a $ so that filling does not move it, then use the results to draw and defend conclusions about a model.
New words
relative reference · a cell address, such as D2, that changes when the formula is filled into other cellsabsolute reference · a cell address with a $ before the column letter and the row number, such as $B$7, that stays the same when the formula is filledlimitation · something that a model leaves out or cannot do, which makes its results less reliable
Explanation
When a formula is filled down, the references move down with it. This is usually what you want. Sometimes one value must be fixed in a single cell, such as a rate that applies to every row. If it is written as B7, then filling down turns it into B8, B9 and so on, and the rows now use the wrong cells. Writing it as $B$7 puts a $ before the letter and before the number, and it stays fixed. An absolute reference lets you change the rate in one place and see every row update, which is what-if analysis. Once the model works, use its totals, MAX, MIN and counts to draw conclusions, and say what the model leaves out. A conclusion should come from the figures, and it should say how far you can trust it.
Kind of reference
Written
In C2, filled down to C3
relative
=B2*B7
=B3*B8
absolute
=B2*$B$7
=B3*$B$7
Worked examples
E2fx=D2*$B$7
A
B
C
D
E
1
Item
Price ($)
Sold
Sales ($)
Fee ($)
2
Kite
8
9
72
3.6
3
Windsock
5
12
60
3
4
Whistle
2
30
60
3
5
Ribbon
1.5
20
30
1.5
6
Total
222
11.1
7
Fee rate
0.05
A stall model. A kite stall pays 5 % of its sales to the market as a fee. The fee rate 0.05 is in B7. E2 holds =D2*$B$7, filled down to E5. The sales in column D are the price times the number sold. The fee for the kites is 72 × 0.05 = 3.6. The totals are 222 in sales and 11.1 in fees.
Cell
With $B$7
Value
Without the $
Value
E2
=D2*$B$7
3.6
=D2*B7
3.6
E3
=D3*$B$7
3
=D3*B8
0
E4
=D4*$B$7
3
=D4*B9
0
E5
=D5*$B$7
1.5
=D5*B10
0
Why the $ matters. The first formula keeps pointing at B7. The second one, without the $, slides down with each row. E3 would use the empty cell B8, which counts as 0, so every fee after the first would be 0. The wrong formula gives the right answer only in the first row, which makes the mistake easy to miss.
Fee rate
Total fee ($)
Sales kept ($)
0.05
11.1
210.9
0.08
17.76
204.24
0.10
22.2
199.8
What-if and conclusions. Only B7 is changed. A fee of 5 % takes 11.1 from the 222 in sales, and 8 % takes 17.76. The conclusion from the figures is that a rise of 3 percentage points costs the stall 6.66 more. A limitation is that the model uses one day’s sales and does not subtract the cost of buying the kites, so the money kept is not profit.
Class activity
Slide the formula (no computer). On squared paper draw a column of five numbers in B2:B6 and a rate in E1. Write a formula in C2 on a small card, =B2*E1, and slide the card down to C3, C4, C5 and C6, rewriting the addresses each time. Do it twice, once with $E$1 and once with E1. Where does the plain E1 go wrong? Then change the rate on the paper and see how many cells have to be changed in each case.
Practice
1
C2 holds =B2*$E$1 and is filled down to C4. E1 holds 3. Write the formulas in C3 and C4 and work out the three values. What would C3 show without the $ signs?
C2fx=B2*$E$1
A
B
C
D
E
1
Pack
Boxes
Cost
Price each
3
2
x
4
12
3
y
7
?
4
z
10
?
C3 holds =B3*$E$1 and C4 holds =B4*$E$1. The values are 4 × 3 = 12, 7 × 3 = 21 and 10 × 3 = 30. Without the $, C3 would be =B3*E2, and E2 is empty, so C3 would show 0.
2
The craft club’s sales add up to 80, and the market charges a fee as a rate in one cell. Work out the total fee at 10 % and at 15 %. Which cell would you change, and how many formulas must be edited?
C2fx=B2*$B$5
A
B
C
1
Item
Sales ($)
Fee ($)
2
Cards
40
?
3
Beads
25
?
4
Paint
15
?
5
Fee rate
0.1
At 10 % the fee is 80 × 0.10 = 8. At 15 % it is 80 × 0.15 = 12. Only the rate cell has to change, and no formula needs to be edited if the formulas use the absolute reference to the rate.
3
Write the formula for each result, and then write two conclusions from the figures. (a) the total tickets (b) the most tickets sold (c) the fewest (d) how many classes sold at least 40.
A
B
1
Class
Tickets
2
7A
34
3
7B
52
4
7C
41
5
7D
52
6
7E
18
(a) =SUM(B2:B6) is 197. (b) =MAX(B2:B6) is 52. (c) =MIN(B2:B6) is 18. (d) =COUNTIF(B2:B6,">=40") is 3. Conclusions: 7B and 7D sold the most with 52 tickets each, so MAX shows the number but not which class, and it is a tie. Three of the five classes sold at least 40 tickets.
4
Name two limitations of the stall model and one improvement for each.
The model uses one day’s data, so it could add a column for each market day. It ignores the cost of the goods, so it could add a cost column and work out the profit. It could also add a check that the fee rate is between 0 and 1.