Hine's Spreadsheets

In this activity, students explore spreadsheet work. The first exercises cover material that they may have considered earlier, for example, on page 14 of Algebra: Book One in this series of Figure It Out. In the spreadsheet on the following page, the formula =B2+4 in cell B3 indicates that the value in cell B3 is 4 more
than the value in B2. The value in cell B2 is 50, so the value in cell B3 is 50 + 4 = 54. After her saving in week 1, Hine has $54 in her savings account. Note that in week 0, Hine has $50. We can interpret this as the amount she has in her account before she began her regular savings programme.

Note that the formula =B3+4 in cell B4 indicates that in week 2, Hine has $4 more in her account than in week 1. Similarly, the formula =B4+4 in cell B5 indicates that in week 3, Hine has $4 more in her account than in week 2, and so on.
In questions 2 and 3, Grandad’s spreadsheet formulae are different from those used by Hine, yet the different spreadsheets produce the same results. The following table shows the link between the two spreadsheets:

Note that in Hine’s formulae, the total saved after any period is calculated by adding $4 to the total saved in the previous week. When the total saved for the previous week is unknown, it is not possible to calculate the current total saved.
Grandad’s formulae, on the other hand, directly calculate the total saved for any given week. They work by calculating how much Hine would have if she saved $4 each week for a given number of weeks and then adding this to the $50 she started with. The total saved in 100 weeks is calculated from 4 x 100 + 50 = 450, and the total saved in 456 weeks is calculated from 4 x 456 + 50 = 1 874. The total saved in x weeks is 4 x x + 50 or 4x + 50. If we use y for the total saved after x weeks, then y = 4x + 50.
Notice also that repeatedly adding 4 as in Hine’s formulae becomes multiplying by 4 in Grandad’s formulae.

So, for example, 4 + 4 + 4 + 4 + 4 = 5 x 4.
You could use this relationship in extension work in which the students could look for a rule for number sequences where the increase or decrease between successive terms does not change. For example, successive terms in the sequence 3, 5, 7, 9, 11, … increase by 2.

As a consequence, we can reason that the rule for the pattern can be expressed algebraically as y = 2x + . The 2 in 2x represents the constant difference, 2, between successive terms. The value that goes in  can be worked out by applying the rule to the first term in the sequence, that is, when x = 1, the term is 3. So, the value that goes in the box is 1, and the rule is y = 2x + 1.
Position in sequence Term Position in sequence Term

Students who are interested in this might like to try finding the rules for the following sequences:
 

Answers to Activity

1. a. Week 0 indicates how much money ($50) Hine had before she started her regular savings in week 1.
b. The value in cell B2 is 50. So the formula =B2+4 gives 50 + 4 = 54 in cell B3.
c. B4: 58 (54 + 4)
B5: 62 (58 + 4)
B6: 66 (62 + 4)
B7: 70 (66 + 4)
B8: 74 (70 + 4)
B9: 78 (74 + 4)
B10: 82 (78 + 4)
B11: 86 (82 + 4)
B12: 90 (86 + 4)
B13: 94 (90 + 4)
B14: 98 (94 + 4)
2. a. Your spreadsheet should be similar to Grandad’s spreadsheet. The values for B2–B14 are the same as those for question 1c.
b. The value in cell A3 is 1. So the formula =4*A3+50 gives 4 x 1 + 50 = 54. This is the same as the value calculated for cell B3 in Hine’s spreadsheet.
c. The values in the savings columns in the two spreadsheets are identical, so both formulae produce the same values.
3. a. B2: 50 (4 x 0 + 50)
B3: 90 (4 x 10 + 50)
B4: 98 (4 x 12 + 50)
B5: 130 (4 x 20 + 50)
B6: 258 (4 x 52 + 50)
B7: 306 (4 x 64 + 50)
B8: 450 (4 x 100 + 50)
B9: 518 (4 x 117 + 50)
b. Practical activity. The values are the same as in question 3a.
c. When Grandad’s formulae are used, it is possible to immediately calculate the amount saved after any number of weeks. When the formulae in Hine’s first spreadsheet are used, the amount saved can only be calculated from the amount
saved in the previous week. So to find the amount saved after 1 000 weeks, the amount saved after 999 weeks must first be known, which means that Hine’s first spreadsheet will use 1 000 rows of calculations to find the savings after 1 000 weeks.
d. Hine can save $4,050. The formula used to directly calculate the amount saved after 1 000 weeks is 4 x 1 000 + 50 = $4 050. Alternatively, you could use the spreadsheet. If you enter 1000 in cell A10 (the next cell free), the value in
B10 will be 4*A10+50, which is 4 x 1 000 + 50 = $4,050 (as before).