Patterns and spreadsheets

Activity 1

In this activity, the students learn how to work with spreadsheet formulae to make calculations that might take considerable time if they were carried out manually. Note that the way your spreadsheets work may be slightly different from the spreadsheets that are shown. You will need to check to see what differences there are before you begin this activity with your students.

In question 1, the students must first notice that Mel saves regular $7 amounts each week starting from week 2. They calculate the total savings in 4 weeks by adding $7 to the savings in week 3. For 5 weeks, the total amount saved is the savings in week 4 plus $7, and so on. Pressing the following sequence of calculator buttons on most calculators will make these calculations:

(On some calculators, the sequence is 12 + + 7 = = = … or 7 + + 12 = = = ...) A new total amount saved is displayed after each press of the equals button.

In question 2, the students need to look for the pattern in the cell formulae. They see that the formula in cell B3 is =B2+7 and then must predict that the formula in cell B8 is =B7+7, the formula in B11 is =B10+7, and so on. These predictions are based on the rule: the value in any cell is the value in the previous cell plus 7.

The power of spreadsheet calculations is demonstrated when the students use their spreadsheet to find Mel’s savings in 52 weeks. In an instant, the computer makes 51 calculations from cell B3 for week 2 to cell B53 for week 52, to give the total savings in 52 weeks as $369.

Activity 2

The students here use their knowledge and understanding of spreadsheets to devise a spreadsheet, such as the one shown in the answers, that will indicate how long it takes Jeff to save $750.

Activity 3

In this activity, Marama makes regular withdrawals of $9 from her account. Note that in week 11, cell B12 will show the savings as –3, that is, an overdraft of $3.

The students might modify this spreadsheet to explore different starting amounts and regular withdrawal amounts.

Activity 4

The students may need to draw pathways on square grid or graph paper, one with 4 lawn squares that can be extended to 5 lawn squares, and so on as necessary, before they see the rule “Add 5 to the previous number of paving stones.” They use this rule to create the spreadsheet formula for cell B3 and so on. The answers to questions 3 and 4 are found by using Fill Down for the Number of paving stones column.

Activity 5

Here, the students design their own spreadsheets and use them to answer the questions. The spreadsheets should look something like the following. In each case, the formula shown at the top is for cell B12.

Note that each spreadsheet above has been extended only to the 13th row or the 12th number in the sequence. The students will need to extend these further using the Fill Down command.

Activity 1

1.

a. If Mel had no money in her account before week 1, she deposited $12 in week 1 and $7 in each of the following 2 weeks. (There is no indication as to whether the account had money in it before week 1.)

b. If Mel continues to deposit $7 each week, she will have saved $82 after 11 weeks. (Weekly totals from week 4 are $33, $40, $47, $54, $61, $68, $75, and $82.)

2.

a. The value in B3 is 7 more than the value in B2. The value in B2 is 12, so the value in B3 is  12 + 7 = 19.

b.

• B8: =B7+7
• B11: =B10+7
• B20: =B19+7
• B43: =B42+7

c. Columns A and B must be filled down to cell A53 and B53 respectively. The value in cell A53 is 52 and in B53 is $369. This is Mel’s savings after 52 weeks.

Activity 2

1.

2.

The spreadsheet shows that after 15 months Jeff has saved $711 and after 16 months he has saved $756. So it takes 16 months for Jeff to save $750.

Activity 3

a. $9

b. The value in cell B5 is 9 less then the value in cell B4. The value in B4 is 69, so the value in B5 is 69 – 9 = 60.

c.

• B6: =B5–9
• B8: =B7–9

d. Nothing, if she withdraws the $6 left in week 10. If the formula is filled down to cell B12, the value will show as –3 (negative 3).

Activity 4

a. Practical activity.

b.

• B7: =B6+5
• B15: =B14+5
• B63: =B62+5
• B75: =B74+5

c. 88 paving stones (cell B18)

d. 36 lawn squares (cell A37)

Activity 5

1.

The 18th number is 119, and the 36th number is 227. The initial formula, for cell B2, is: =B1+6.

2.

The 27th number is 573, and the 43rd number is 893. The initial formula, for cell B2, is: =B1+20.

3.

The 24th number is 86.5, and the 87th number is 307. The initial formula, for cell B2, is: =B1+3.5.