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
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 referenceWrittenIn C2, filled down to C3
relative=B2*B7=B3*B8
absolute=B2*$B$7=B3*$B$7
Worked examples
E2fx=D2*$B$7
ABCDE
1ItemPrice ($)SoldSales ($)Fee ($)
2Kite89723.6
3Windsock512603
4Whistle230603
5Ribbon1.520301.5
6Total22211.1
7Fee rate0.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.
CellWith $B$7ValueWithout the $Value
E2=D2*$B$73.6=D2*B73.6
E3=D3*$B$73=D3*B80
E4=D4*$B$73=D4*B90
E5=D5*$B$71.5=D5*B100
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 rateTotal fee ($)Sales kept ($)
0.0511.1210.9
0.0817.76204.24
0.1022.2199.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
ABCDE
1PackBoxesCostPrice each3
2x412
3y7?
4z10?
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
ABC
1ItemSales ($)Fee ($)
2Cards40?
3Beads25?
4Paint15?
5Fee rate0.1
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.
AB
1ClassTickets
27A34
37B52
47C41
57D52
67E18
4
Name two limitations of the stall model and one improvement for each.