Patterns, Rules and Spreadsheets

Activities one to four

In this activity, students interpret, make, and then solve problems using spreadsheets. It builds on the earlier spreadsheet activity on page 1 of the student book.
In question 1, the students see and use two ways to generate the same number sequences using computer spreadsheets. Both methods model what happens to Hannah’s initial savings of $63 if she saves a further $8 each week.
When method 1 is used, the formula in any cell calculates a value that is 8 more than in the cell directly above it. So the formula =B4+8 in cell B5 simply adds 8 to the value in cell B4, the formula =B5+8 in cell B6 adds 8 to the value just calculated for cell B5, and so on.
When method 2 is used, students see that the value calculated for any cell is independent of the value in the cell directly above it (in contrast to the first method). The second method works by multiplying the number of weeks by 8 and adding 63. So, for example, the formula =8*A9+63 in cell C9 multiplies the value in A9 by 8 and then adds 63. The value in A9 is 6 (6 weeks), so the value in C9 is 8 x 6 + 63 = 111. So after 5 weeks, Hannah will have saved $103.
Different spreadsheet applications have different ways of working. In some applications, the Fill Down command in the Calculate menu is used to enter formulae. Other applications operate automatically. Formulae are entered by “pulling” the first cell down or across as required.

Note with Activity One that when the first method is used to calculate savings after 52 weeks, the savings after each week from week 1 to week 52 must be calculated. While this doesn’t take long on a spreadsheet, it is actually quicker and easier to read as a formula if the second method is used. For example, Hannah’s savings after 52 weeks are calculated from 8 x 52 + 63, that is, $8 for each of 52 weeks plus the $63 in the account at the start. Here a single calculation is made instead of the 52 calculations with method 1.
In Activity Two, question 1, the parts –3*A2, –3*A3, –3*A4, and so on of the formulae in successive cells in column B indicate that successive values in the sequence decrease by 3. So Tom is skip-counting backwards in 3s from 501. The 145th number in Tom’s sequence will be 504 – 3 x 145 = 69. And in question 2, the
parts +12*A2, +12*A3, +12*A4, and so on of the formulae in column B indicate that successive values in the sequence increase by 12. So Marnie is skip-counting forwards in 12s from 17. The 879th number in Marnie’s sequence will be 5 + 12 x 879 = 10 553. Note that, as with the second method in Activity One, the formulae
used in both questions in Activity Two require only a single calculation to work out the value in any cell.
In Activity Four, the students make spreadsheets to compare charges for gardening services offered by Mika and Hine. The spreadsheets in the Answers show the effects of firstly making a change to Mika’s hourly rate and then a change to Hine’s fixed travelling charge.
For more than 1 hour’s work, Hine’s charges are less than Mika’s. Working for 0 hours is really meaningless, so Hine’s new charges will always be cheaper or, in one case only, the same as Mika’s. Students who reason successfully with the tasks above might see if they can find a way that Mika could respond by altering his
charges to better compete with Hine. This challenge will be more interesting if conditions are placed upon it, for example: “How can Mika alter his travel charge or hourly rate so that he is cheaper than Hine for up to 3 hours’ work, and charges the same as Hine for 4 hours’ work?”
The formulae in the spreadsheets above have a direct relationship with algebra and algebraic thinking. For example, the formula used in cell B2 for Mika’s original charging plan is =13+17*A2. For cell B3, the formula is =13+17*A3, and for cell B4, the formula is =13+17*A4, and so on. This set of formulae can be replaced
by a single algebraic formula, y = 13 + 17 x x, or more simply, y = 17x + 13. Here, y stands for the total charge for x hours’ gardening.
Equations of the form y = 17x + 13 and y = 13x + 25 (the two equations  corresponding to Mika and Hine’s initial charging schemes) are called linear equations because they describe straight lines.
As an extension exercise, the students could graph these lines to highlight the difference in Mika and Hine’s charges. By plotting both lines on the same grid, the students will easily see the contrast in their charges.
For instance, a graph showing a comparison of their first charging schemes is:

This displays the information given in the spreadsheet very clearly. For example, we can see from the graph that the lines cross when Mika and Hine charge the same amount for the same period worked (3 hours for $64), and that Mika is cheaper for under 3 hours, while Hine is cheaper for over 3 hours.

Answers to Activity

Activity one
a. $63
b. $8
c. The savings that the formulae work out will be the same in both Savings columns:

d. $479
e. The calculation 8 x 75 + 63 = 663 indicates the savings after 75 weeks. That is, $8 per week for 75 weeks, plus the $63 Hannah started with.
f. i. 8 x 83 + 63 = $727
ii. 8 x 147 + 63 = $1,239
g. Answers may vary, but the second method is more efficient because it calculates directly rather than using Fill Down. For example, for 52 weeks with method 1, Hannah first needs to have filled down to 51 weeks, but with the second method,
she just needs to calculate 8 x the week number + 63.
Activity Two
1. a. 501. (The value in cell A2 is 1, so the formula =504–3*A2 in cell B2 produces
504 – 3 x 1 = 501. The starting number is therefore 501.)
b. 501, 498, 495, 492, 489, 486, 483, 480.  (The –3 in the formula indicates that Tom is skip-counting backwards in 3s.)
c. i. 414. (504 – 3 x 30)
ii. 285. (504 – 3 x 73)
iii. 69. (504 – 3 x 145)
2. a. 17. (The value in cell A2 is 1, so the formula =5+12*A2 in cell B2 produces 5 + 12 x 1 = 17.)
b. 17, 29, 41, 53, 65, 77, 89, 101. (The +12 in =5+12*A2 indicates that Tom is skipcounting forwards in 12s.)
c. i. 1 193. (5 + 12 x 99)
ii. 3 077. (5 + 12 x 256)
iii. 10 553. (5 + 12 x 879)
Activity Three

1a.

The spreadsheet above shows the first 8 numbers and the formula =16+7*A2 to
calculate the first number, 23, in cell B2. (The formulae used may vary, but the sequence of numbers will be the same.) Successive numbers increase by 7.
The formula =16+7*A3 goes in cell B3 to calculate the 2nd number, the formula
=16+7*A4 goes in cell B4 for the 3rd number, and so on.

2a.

b. i. 275. (16 + 7 x 37)
ii. 1 325. (16 + 7 x 187)
iii. 8 969. (16 + 7 x 1 279)
Formulae may vary. The spreadsheet above shows the first 8 numbers and the formula =10010–9*A2 to calculate the first number, 10 001, in cell B2. Successive numbers decrease by 9.
The formula =10010–9*A3 goes in cell B3 to calculate the 2nd number, the formula
=10010–9*A4 goes in cell B4 for the 3rd number, and so on.
b. i. 9 506. (10 010 – 9 x 56)
ii. 9 263. (10 010 – 9 x 83)
iii. 1 019. (10 010 – 9 x 999)
Activity Four

1a.

(The formula in cell C2 is =25+13*A2. The value in cell A2 is 0, so the formula calculates the value in cell C2 as 25. This is Hine’s travelling charge of $25.)
b. The charges for 3 hrs are the same, $64. For any time less than 3 hrs, Mika’s charge is less than Hine’s. For any time greater than 3 hrs, Hine’s charge is less than Mika’s.

c.

The formula in cell B2 changes to =13+16*A2. Charges for 4 hrs are now the same, $77. For any time less than 4 hrs, Mika’s charge is less than Hine’s. For any time greater than 4 hrs, Hine’s charge is less than Mika’s.
d.

The formula in cell C2 changes to =16+13*A2. Now the charge for both gardeners’ services for 1 hr is the same, $29. For more than 1 hr, Hine’s charge is less than Mika’s.