Spreadsheet Activities

ICT

Home

Investigating data from a survey of a group of pupils

survey

This chart shows measurements and observations gathered by a KS2 class who were investigating individual differences. Use the Sort and Graphing tools to answer these questions:

  1. Do all people with brown eyes have dark hair?
  2. Do all people with Blonde hair have blue eyes?
  3. Is there a link between height and weight of people in the class?
  4. Do most tall people have big feet?
  5. Who is the tallest boy in the class?

Download the file - survey.xls

To answer question 1, you will need to sort the data on the Colour of Eyes column to see which hair colours are linked to brown eyes. To do this:

  1. Select any cell in the data part of the spreadsheet and select Sort... from the Data menu.
  2. Select Colour of Eyes from the drop-down menu in the resulting dialog.
  3. Click OK and the data will be sorted on eye colour.
  4. By looking at the Colour of Hair column, you should be able to see which hair colours are linked to people with brown eyes.

To answer question 2, you will need to sort the data on the Colour of Hair column to see which eye colours are linked to blonde hair. To do this:

  1. Select any cell in the data part of the spreadsheet and select Sort... from the Data menu.
  2. Select Colour of Hair from the drop-down menu in the resulting dialog.
  3. Click OK and the data will be sorted on hair colour.
  4. By looking at the Colour of Eyes column, you should be able to see which eye colours are linked to people with blonde hair

To answer question 3, you will need to draw a scattergraph to show any possible correlation between height and weight in the group. To do this:

  1 Click in cell D1 and, holding down the mouse button, drag the selection box to cell E20. This will highlight all the cells containing relevant data.
  2 Click on the Chart Wizard icon in the toolbar.
  3 When the Chart Wizard dialog box appears, select the XY (Scatter) type and the "Scatter - Compares pairs of values" graph.
  4 Click on the Next > buttons - you could give the graph a title and label the axes at Step 3 of 4 - and press Finish to display the graph.
  5 The graph will be displayed showing any possible link in terms of points arranged along a diagonal.
  6 If a link is evident, draw in a Line of Best Fit using the line tool on the Drawing toolbar at the foot of the screen. If the Drawing toolbar is not present on-screen, select it using Customize... in the Tools menu.

To answer question 4, you will need to draw a scattergraph to show any possible correlation between height and shoe size in the group. To do this:

  1
Click in cell D1 and, holding down the mouse button, drag the selection box to cell D20 - this will select the height column.
Now hold down the CONTROL (Ctrl) key. Click in cell F1 and, holding down the mouse button, drag the selection box to cell F20 - this will select the shoe size column.
  2 Click on the Chart Wizard icon in the toolbar.
  3 When the Chart Wizard dialog box appears, select the XY (Scatter) type and the "Scatter - Compares pairs of values" graph.
  4 Click on the Next > buttons - you could give the graph a title and label the axes at Step 3 of 4 - and press Finish to display the graph.
  5 The graph will be displayed showing any possible link in terms of points arranged along a diagonal.
  6 If a link is evident, draw in a Line of Best Fit using the line tool on the Drawing toolbar at the foot of the screen. If the Drawing toolbar is not present on-screen, select it using Customize... in the Tools menu.

To answer question 5, you will need to sort the data on the height column. To do this:

  1. Select any cell in the data part of the spreadsheet and select Sort... from the Data menu.
  2. Select Height cm from the drop-down menu in the resulting dialog. Click the Descending button to ensure that the data is sorted with the tallest person at the top of the list.
  3. Click OK and the data will be sorted on height.


Spreadsheets Home page