Introduction to Spreadsheets

Introduction

You will need to be able to:

• access Excel in your system

Language used:

A favourite statistical topic with junior pupils is the collection of data such as “the most popular colour of cars”, “the number of different coloured Pebbles in packets” or “the most popular type of pet”.

Any of these will work well, however the lure of chocolate makes Pebble counting (and eating) very popular. There is of course the requirement that each pupil (pair or small group of pupils) has a small packet of Pebbles. A beginning exercise to try and discover what a typical (or average), small packet of Pebbles is like can be conducted using such questions as:

How many Pebbles are there in a box?
How many different coloured Pebbles are there? What are they?
What is the most common coloured Pebble?
What fraction of Pebbles are red?
What is the percentage of orange Pebbles?
Write the number of yellow Pebbles as a decimal fraction.
What is the ratio of green Pebbles to blue Pebbles?

These questions give a chance to test some basic calculation skills and further work on estimation can be carried out, such as guessing the numbers before counting and finding the differences.
The results obtained also allow us to look at the concept of variation,which is the basis of all statistical investigation.

Exercise

Have the pupils collect their data in a table similar to the one below.

Using an OHP of a spreadsheet or by drawing a similar grid on the board, collect in the data from the pupils and enter it on your spreadsheet/grid to show how the data can be arranged. An example is shown below in figure (i). I would suggest covering the borders and attempting to get the students to come up with a method of cell reference (label). Introduce the names rows and columns and the way the cells are actually named in excel.

figure (i)

Using the class set of data get pupils to answer questions such as: Which cell has

the most light brown Pebbles?
the least dark brown Pebbles?
the most pink Pebbles?

In this way cell references (labels) can be practised.

The next move is to the computer where the pupils can enter the data into their own spreadsheet. They must be shown how to select (highlight) a cell using the mouse to move the mouse pointer or by the arrow keys. One problem that pupils encounter is ensuring that the cell is wide enough for the contents. (Editing cell width is covered fully in the Record Shop worksheet)

In the column headed Total the pupils can sum their Pebbles numbers. Ask about using a formula, which refers to the cells that they use. An example is
=B2 + C2 + D2 + E2 + F2 + G2 + H2 + I2 + J2
(The use of formulas is introduced in the Multiplication Investigation worksheet)

Using such formulas helps pupils to differentiate between the cell labels and the actual values.

Discussion of the alternative formula =SUM(B2:J2) and the shortcut sigma button, at right, may be taken up here. (All types of summation are covered fully in the Magic Square worksheet). The formula  for calculating the mean could even be discussed with a more able class and a cell for the average number of each colour included as in figure (i).

Further questions can be asked at this stage to test spreadsheet understanding such as:

How many Pebbles are there in a typical (average) box of Pebbles?

Who had the most of each coloured Pebble?
Which colour appeared most?
Which colour appeared least?
Write out in words what the contents of cell D4 describes.

Further work with calculations can also be included at this stage. For example:

What fraction of Pebbles are purple?
What is the ratio of
red:orange:yellow:green:blue:light brown:dark brown:pink:purple
in a typical (average) box?
If we took a box containing 10000 Pebbles how many reds would you expect to find? How many orange Pebbles? Yellows?

These questions may be answered by writing equations, which may then be transformed into spreadsheet formula and used on the computer.

The Pebbles themselves can be used to introduce graphs in a physical sense. Take your own box of Pebbles and tip them onto a prepared OHP sheet (See page 4). Arrange the Pebbles by colour as in the example in figure (ii) below. Don’t leave them on the OHP too long as they may melt.

This type of activity can help pupils grasp the idea of grouping similar objects in a sensible way. The drawing of graphs using the spreadsheet can be brought in at this point and discussed.