Study Guide

Collecting and Analysing Data

A small project for a known user: planning what to find out, collecting data with a survey, organising it in a spreadsheet with formulas, choosing an honest chart, drawing fair conclusions and presenting them to the person who asked.

Lesson 7.4 Digital Literacy and Online Safety

For: Key Stage 3 (about ages 11 to 14), and anyone starting out

Stage: Think

Someone needs an answer

Ms Okafor, the head of PE at the fictional Brightwater School, has money for two new after-school clubs. She asks a Year 8 class: “Which clubs would pupils actually come to? I need a one-page summary by Friday.” Ms Okafor is the user: the person the work is for.

Think it through

Before collecting anything, what do you need to find out about what Ms Okafor wants, and what do you need to find out from pupils?

Show a model answer

From Ms Okafor: which clubs she is choosing between, what she will do with the answer, how detailed it should be (one page) and the deadline. From pupils: which club they would choose, and whether they could come on the days available. Knowing the question first stops the class collecting data that nobody needs.

Stage: Discover

Planning and choosing applications

Think it through

The class must collect answers, count and compare them, make a picture of the results and write a one-page summary. What kind of program might suit each job?

Show a model answer

A form or survey tool to collect answers; a spreadsheet to count and compare; a chart tool, often part of the spreadsheet, for the picture; and a document or slide to present. Real projects combine several applications, with each used for what it does best.

A simple plan states, in order: the user’s question, the data to collect, the applications to use, and how the result will be presented. For Ms Okafor:

JobApplication
Collect answers from the classA survey form
Organise and calculateA spreadsheet
Show the resultsA chart made in the spreadsheet
Present to the userA one-page document

Check your understanding

The class wants to add up how many pupils chose each club, and work out an average. Which application suits that job best?
Show the answer

Answer: B. A spreadsheet, because it can calculate with data using formulas.

Each application has a job; a project uses the one that fits each.

Stage: Explain

Asking good questions

The class drafts three survey questions:

Question 1. “Football is brilliant, so which club will you join?”

Question 2. “Which club would you join: football, dance, chess or climbing?”

Question 3. “Do you like sport and music?”

Think it through

Which of the three questions would you keep, and what is wrong with each of the others?

Show a model answer

Keep Question 2: it is clear and offers a fixed set of choices, which are easy to count. Question 1 pushes people towards football (a leading question). Question 3 asks two things at once, so a pupil who likes sport but not music cannot answer it properly, and it does not help choose a club anyway.

A good question asks one thing, does not push towards an answer, and offers fixed choices where possible, because those are easy to count. Ask the people the answer is about: here, pupils who could actually join.

Check your understanding

“Don’t you agree that a Thursday club would be better than a Monday club?” What is the main problem?
Show the answer

Answer: A. It pushes the reader towards an answer, so the result would not be fair.

Wording affects answers, so write neutral questions.

Stage: Experiment

Organising data in a spreadsheet

Think it through

Twelve pupils each answered three questions: their club choice, hours of sport a week, and whether they could come on Thursday. How would you lay out the answers in a table so that they are easy to count?

Show a model answer

One row per pupil and one column per question, with a heading at the top of each column. Then every answer sits in its own place, and a column can be counted or added in one go.

A spreadsheet is a program that holds data in a grid and can calculate with it. Each box is a cell, named by its column letter and row number: the first club answer, Football, is in cell B2. Here is the class’s fictional data, as it would look in a spreadsheet:

RowA: PupilB: ClubC: Hours of sportD: Can come Thursday
1PupilClubHoursThursday
21Football3Yes
32Dance5Yes
43Football2No
54Chess1Yes
65Dance6Yes
76Football4Yes
87Climbing3No
98Dance4Yes
109Football2Yes
1110Chess0No
1211Climbing5Yes
1312Dance1No

A formula is an instruction that starts with an equals sign and works out a result from cells. Four useful ones, described in words:

  • =SUM(C2:C13) adds every number from cell C2 to C13.
  • =AVERAGE(C2:C13) adds them and divides by how many there are.
  • =MAX(C2:C13) finds the largest.
  • =COUNTIF(B2:B13,"Football") counts how many cells in B2 to B13 say Football.

Some spreadsheet programs use a semicolon instead of a comma between the parts of a formula.

  1. Read the range. C2:C5 means cells C2, C3, C4 and C5.
  2. Look up each value. They are 3, 5, 2 and 1.
  3. Do what the formula says. SUM adds: 3 + 5 + 2 + 1 = 11.
  4. Write the result with its meaning. 11 is the total hours of sport for pupils 1 to 4.

Checking by hand like this is how you catch a formula that points at the wrong cells.

What does each formula give, using the data above?
FormulaResult
=SUM(C2:C5)11
=SUM(C2:C13)
=AVERAGE(C2:C13)
=MAX(C2:C13)
=COUNTIF(B2:B13,"Football")
=COUNTIF(D2:D13,"Yes")

    Show the completed table
    FormulaResult
    =SUM(C2:C5)11
    =SUM(C2:C13)36
    =AVERAGE(C2:C13)3
    =MAX(C2:C13)6
    =COUNTIF(B2:B13,"Football")4
    =COUNTIF(D2:D13,"Yes")8

    Check your understanding

    Ms Okafor asks how many pupils chose Dance. Which formula answers that?
    Show the answer

    Answer: B. =COUNTIF(B2:B13,"Dance"), which counts the cells that say Dance.

    Pick the formula by the question: add, average, find the largest or count matches.

    Stage: Predict

    Choosing a chart

    Think it through

    Ms Okafor will glance at one picture of how many pupils chose each of the four clubs. What would the picture look like, and why?

    Show a model answer

    Four bars, one for each club, with the height showing the number of pupils. Bars of different heights are quick to compare side by side.

    A chart is a picture of data that makes comparisons quicker to see than a table of numbers. Three common kinds:

    • Bar chart: compares amounts between separate groups, such as clubs.
    • Line chart: shows how something changes over time, such as the temperature each month.
    • Pie chart: shows how a whole splits into a few parts, such as yes and no answers.

    The club counts as bars (each # is one pupil):

    Football   ####   4
    Dance      ####   4
    Chess      ##     2
    Climbing   ##     2

    Check your understanding

    A fictional pupil records the school playground’s temperature at noon on each day of a month. Which chart shows it best?
    Show the answer

    Answer: C. A line chart, because it shows how the temperature changes over time.

    Match the chart to the question: compare groups, follow a change or show parts of a whole.

    Stage: Apply

    Is the chart honest?

    A fictional bar chart of the club counts has a vertical axis that starts at 1, not 0. The Football bar is 3 units tall and the Chess bar is 1 unit tall.

    Think it through

    Football has 4 pupils and Chess has 2. How many times taller does the Football bar look than the Chess bar, and how many times more pupils chose it really?

    Show a model answer

    The bars look three times as tall (3 against 1), but Football really has twice as many pupils (4 against 2). Starting the axis at 1 exaggerates the difference. An honest chart starts the axis at 0, labels both axes and has a title.

    Apply it

    A fictional chart shows 40 pupils in Class A and 45 in Class B, using a vertical axis that starts at 35. Class B’s bar looks twice as tall. What is the best way to improve the chart?
    Show the answer

    Answer: B. Start the axis at 0, and label the axes and give the chart a title.

    A chart should let the reader see the true size of a difference.

    Stage: Explain

    What can you conclude?

    The results: Football 4, Dance 4, Chess 2, Climbing 2, from 12 pupils in one Year 8 class. Eight of the 12 said they could come on Thursday.

    Think it through

    Write one conclusion for Ms Okafor that the data supports, and one thing the data cannot tell her.

    Show a model answer

    Supported: Football and Dance were joint most popular, with four pupils each, and eight of the twelve could come on Thursday. Not supported: that these are the most popular clubs across the whole school, because only 12 pupils from one class were asked. A fair conclusion mentions how many people were asked.

    Check your understanding

    Which statement does this data support?
    Show the answer

    Answer: C. In this class, Football and Dance were equally popular, ahead of Chess and Climbing.

    Say what the data shows, for whom, and no more.

    Stage: Apply

    Present it, then judge it

    Think it through

    Ms Okafor has one page and little time. What would you put at the top of the page, and what would you leave out?

    Show a model answer

    At the top, the answer to her question: for example, “Football and Dance were the most popular choices in this class.” Then the chart, a sentence on how many pupils were asked, and any limits. Leave out detail that does not help her decide, such as every pupil’s individual answer.

    To evaluate the project, go back to what the user asked for. Did it answer her question? Is it one page? Can she read it quickly? Is it honest about its limits? The best test is to ask the user, as in the previous lesson.

    Check your understanding

    Which is the best way to evaluate the finished one-page summary?
    Show the answer

    Answer: A. Check it against what Ms Okafor asked for, and ask her whether it helps her decide.

    Judge the work against what the user needed, and ask the user.

    Stage: Consolidate

    Try a new project yourself

    A fictional librarian, Mr Singh, asks how many books Year 7 pupils read last month. Six pupils answer, and the answers go in cells C2 to C7: 2, 5, 3, 4, 6, 4.

    What does each formula give for Mr Singh’s data?
    FormulaResult
    =SUM(C2:C7)
    =AVERAGE(C2:C7)
    =MAX(C2:C7)
    =COUNTIF(C2:C7,4)

      Show the completed table
      FormulaResult
      =SUM(C2:C7)24
      =AVERAGE(C2:C7)4
      =MAX(C2:C7)6
      =COUNTIF(C2:C7,4)2

      Apply it

      Mr Singh wants to compare how many books each of the six pupils read. Which chart, and which note to include with it, is best?
      Show the answer

      Answer: D. A bar chart with an axis from 0, with a note that only six pupils were asked.

      A good project chooses a suitable chart, draws it honestly and states its limits.

      Think it through

      In your own words, explain the steps from a user’s question to a finished summary, and say which step you think matters most.

      Show a model answer

      A good answer follows the order: find out the user’s question, plan the data and applications, collect it with fair questions, organise and calculate in a spreadsheet, choose an honest chart, draw supported conclusions, present to the user and evaluate against their needs. Which matters most is a judgement; many pick the first, because every later step depends on it.

      • Start from the user’s question, then plan the data, the applications and the presentation.
      • Good survey questions ask one thing, are neutral and use fixed choices.
      • A spreadsheet holds data in cells; a formula starts with = and works out a result: SUM adds, AVERAGE averages, MAX finds the largest, COUNTIF counts matches. Check by hand.
      • A chart should suit the question, start at 0 for bars, and be labelled.
      • Conclusions say what the data shows, for whom, and no more. Evaluate against the user’s needs.

      Sources and scope

      • What this lesson is. The fourth of four lessons in Digital Literacy and Online Safety, written for this site. Its curriculum link is provisional and implies no endorsement by any examination board or by the Department for Education.
      • Original and fictional. Ms Okafor, Mr Singh, the school, the survey and all the figures are invented, and each value used in a formula has been checked by adding and counting by hand.
      • Simplified on purpose. Twelve and six respondents are far fewer than a real survey would use. Spreadsheet programs differ slightly in how formulas are written (for example, a comma or a semicolon), and the lesson does not teach any one program’s buttons or menus.