Using spreadsheets as investigative tools with primary mathematics pupils

James R.M. Paul
Department of Primary Education
Rhodes University, East London

Internet and Educational Computing Conference
Cape Town
26-27 September 1997

Queries and comments to:
monty@dolphin.ru.ac.za

Mathematical investigations form an important part of the constructivist learning approach recommended by Curriculum 2005. Designed to provide pupils with a feel for numbers and how they work, investigations can take many forms, such as the examination of number sequences, the relationship between area and perimeter and triangular numbers. Spreadsheets lend themselves as tools in a variety of investigations, especially where a large number of calculations are required to reveal a trend or pattern, or where the graphing of results helps children to see what is happening. One investigation that I have found well-suited to solution using the electronic spreadsheet will be described here, and others mentioned.

Aunt Lucy's Legacy

The Problem:

Dear Lindsay

Now that I am getting on (I turn 70 today) I want to give you some of my money. I shall give you a sum each year, starting now. You can choose which of the following schemes you would like to use.

1. R100 now, R90 next year, R80 the year after, and so in.
2. R10 now, R20 next year, R30 the year after that, and so on.
3. R10 now, one and a half as much next year, one and a half as much again the year after, and so on.
4. R1 now, R2 next year, R4 the year after, R8 the year after that, and so on.

Of course, the scheme can only operate while I am alive. I look forward to hearing which scheme you choose, and why.

Sincerely

Aunt Lucy

This particular investigation comes from the 1989 Hertfordshire Information Technology Across the Curriculum (Mathematics and Data Handling) project document. The pencil and paper approach can be used but could become tedious and boring. This makes it particularly suited to solution by spreadsheet. As will be seen, five main calculations are required, but need to be repeated many times. The copying facility of the spreadsheet resolves this problem, enabling the children to enter the relevant formulas once only (see figure 1). Once copied to the relevant range, the table (see figure 2) can be examined to provide the answer to a variety of questions. The software also enables easy graphing of figures, providing a graphical model for interpretation (see figure 3).

I always begin by giving each group a paper template (See ap pendix 1), on which they work in the traditional method, using calculators After the groups have discussed the problem and are happy about the way to solve it, I allow them to begin, working across the page, and stop them after they have completed about 3 calculations. I then ask them what they notice about the calculations. Hopefully the reply is that the kinds of calculations they are doing are the same each time. I then suggest that using a spreadsheet might aid them with these calculations.

I provide a template spreadsheet similar to the one in appendix 1 (see figure 1 below). Some instruction is required if the children have not used a spreadsheet before, with respect to the way that formulae are entered.

Figure 1

A

B

C

D

E

F

G

H

I

J

1	Aunt Lucy's Legacy

2	Years	Opt 1	Tots	Opt 2	Tots	Opt3	Tots	Opt 4	Tots
3	1	R100	R100	R10	R10	R10	R10	R1	R1
4	2	=C3-10	=D3+c4	=E3+10	=F3+e4	=g3*1.5	=H3+g4	=I3*2	=J3+I4
5	3
6	4
7	5
8	6
9	7
10	8
11	9
12	10
13	11
14	12

Back

Once the children are happy with the way in which formulae are composed, I allow them to enter the relevant into the spreadsheet, in row 2. I follow by showing them how to copy the formulas across the range, to get the results. (See figure 2 below)

Figure 2

				Aunt Lucy's Legacy
Years	Opt 1	tots	Opt 2	tots	Opt 3	tots	Opt 4	tots
1	R100	R100	R10	R10	R10	R10	R1	R1
2	R90	R190	R20	R30	R15	R25	R2	R3
3	R80	R270	R30	R60	R23	R48	R4	R7
4	R70	R340	R40	R100	R34	R81	R8	R15
5	R60	R400	R50	R150	R51	R132	R16	R31
6	R50	R450	R60	R210	R76	R208	R32	R63
7	R40	R490	R70	R280	R114	R322	R64	R127
8	R30	R520	R80	R360	R171	R493	R128	R255
9	R20	R540	R90	R450	R256	R749	R256	R511
10	R10	R550	R100	R550	R384	R1,133	R512	R1,023
11			R110	R660	R577	R1,710	R1,024	R2,047
12			R120	R780	R865	R2,575	R2,048	R4,095
13			R130	R910	R1,297	R3,872	R4,096	R8,191

Back

The data revealed is easily graphed (see figure 3).

Figure 3

Back

The children can look both at the graph and the table to find the answers to the questions that follow.

1. If Aunt Lucy lived another five years, what option would be best?
2. If she lived for 10 years, what option would be best?
3. For how many years is option 1 the best?
4. How many years will it take for option 3 to outperform any other options?
5. How long does it take option 4 to outperform the other options?
6. What is the main limitation of option 1?
7. What can you say about the way numbers behave when they are doubled?
8. How many years would it take for the total in option 4 to exceed R200 000? Is there an easy way of working this out?
9. Are there any other issues that you would like to raise?

The interpretation of data is an important aspect of life outside of school and my main interest in doing this kind of exercise is to provide the children with an opportunity to master the processes involved in interpreting tabled and graphical information. The spreadsheet is an ideal tool for exercises of this nature, freeing the children from the tedium of repeated calculation and allowing them to concentrate on the information revealed and the process of interpreting it. I do go on to look at spreadsheet design once several exercises of this nature have been completed and once the children are comfortable with the formatting and copying of formulae. I have also found that children who have had the opportunity to work with partly completed spreadsheet templates learn to use the software more easily than when they are introduced to them via the ‘design from scratch’ model.

Another exercise of a similar nature suited to the upper primary classroom, is included as appendix 2.

Appendix 1

Dear Lindsay

Now that I am getting on (I turn 70 today) I want to give you some of my money. I shall give you a sum each year, starting now. You can choose which of the following schemes you would like to use.

1. R100 now, R90 next year, R80 the year after, and so in.

2. R10 now, R20 next year, R30 the year after that, and so on.

3. R10 now, one and a half as much next year, one and a half as much again the year after, and so on.

4. R1 now, R2 next year, R4 the year after, R8 the year after that, and so on.

Of course, the scheme can only operate while I am alive. I look forward to hearing which scheme you choose, and why.

Sincerely

Aunt Lucy

Aunt Lucy's Legacy

Years	Opt 1	Totals	Opt 2	Totals	Opt 3	Totals	Opt 4	Totals
1	R100	R100	R10	R10	R10	R10	R1	R1
2	R90	R190	R20	R30	R15	R25	R2	R3
3
4
5
6
7
8
9
10
11
12
13
14

Back

Appendix 2: Box making exercise

You have been invited to a party and are given a piece of card 30 cm x 30 cm in size, with which to make a box to hold sweets. What dimensions must the box be to hold a maximum number of sweets?

Equipment:

card 30 x 30 cm
scissors
paper template
spreadsheet template

What size cuts should you make to maximise the volume of the box?

Paper Template

Box making exercise

cut size	side 1	side 2	base area	volume
1	28	28	784	784
2
3

Spreadsheet template

A	B	C	D	E	F	G
1	Paper size	cut size	side 1	side 2	base area	volume
2	30	1	+b2-c2*2	=d2	+d2*e2	+f2*c2
3		2
4		3
5		4
6		5
7		6
8		7
9		8
10		9
11		10
12		11
13		12
14		13
15		14
16		15

Completed spreadsheet

paper length

cut size

side 1

side 2

base area cm2

vol cm3

30

1

28

28

784

784

	2	26	26	676	1352
	3	24	24	576	1728
	4	22	22	484	1936
	5	20	20	400	2000
	6	18	18	324	1944
	7	16	16	256	1792
	8	14	14	196	1568
	9	12	12	144	1296
	10	10	10	100	1000
	11	8	8	64	704
	12	6	6	36	432
	13	4	4	16	208
	14	2	2	4	56
	15	0	0	0	0

Back

The data revealed suggests that the cut should be 5 cm, giving a turn up of 20 cm. Questions that could be asked include:

1. What proportion of the length does the cut consist of?
2. Does this proportion hold true for all card sizes?
3. Try the exercise with a piece of card twice as long as it is wide. What do you find?
4. Is this the same for all such shapes?
5. Can you make any predictions for any other shapes of card?
6. Are there any other things that you notice worth commenting about?