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?
- Hint 1: What does she want to decide?
- Hint 2: What form should the result take, and by when?
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?
- Hint 1: Which job needs calculating?
- Hint 2: Which needs a form that people fill in?
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:
| Job | Application |
|---|---|
| Collect answers from the class | A survey form |
| Organise and calculate | A spreadsheet |
| Show the results | A chart made in the spreadsheet |
| Present to the user | A one-page document |
Check your understanding
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?
- Hint 1: Does a question push the reader towards an answer?
- Hint 2: Does it ask two things at once?
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
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?
- Hint 1: What goes in each row?
- Hint 2: What goes in each column?
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:
| Row | A: Pupil | B: Club | C: Hours of sport | D: Can come Thursday |
|---|---|---|---|---|
| 1 | Pupil | Club | Hours | Thursday |
| 2 | 1 | Football | 3 | Yes |
| 3 | 2 | Dance | 5 | Yes |
| 4 | 3 | Football | 2 | No |
| 5 | 4 | Chess | 1 | Yes |
| 6 | 5 | Dance | 6 | Yes |
| 7 | 6 | Football | 4 | Yes |
| 8 | 7 | Climbing | 3 | No |
| 9 | 8 | Dance | 4 | Yes |
| 10 | 9 | Football | 2 | Yes |
| 11 | 10 | Chess | 0 | No |
| 12 | 11 | Climbing | 5 | Yes |
| 13 | 12 | Dance | 1 | No |
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.
- Read the range. C2:C5 means cells C2, C3, C4 and C5.
- Look up each value. They are 3, 5, 2 and 1.
- Do what the formula says. SUM adds: 3 + 5 + 2 + 1 = 11.
- 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.
| Formula | Result |
|---|---|
| =SUM(C2:C5) | 11 |
| =SUM(C2:C13) | |
| =AVERAGE(C2:C13) | |
| =MAX(C2:C13) | |
| =COUNTIF(B2:B13,"Football") | |
| =COUNTIF(D2:D13,"Yes") |
- Write out the cells the formula covers, then do what it says.
- For COUNTIF, count the rows that match.
Show the completed table
| Formula | Result |
|---|---|
| =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
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?
- Hint 1: Which would you compare most easily: bars, a line or slices?
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 ## 2Check your understanding
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?
- Hint 1: Compare the heights, 3 and 1.
- Hint 2: Compare the real counts, 4 and 2.
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
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.
- Hint 1: Stick to what the numbers show.
- Hint 2: Who was asked, and how many?
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
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?
- Hint 1: What does she need to decide?
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
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.
| Formula | Result |
|---|---|
| =SUM(C2:C7) | |
| =AVERAGE(C2:C7) | |
| =MAX(C2:C7) | |
| =COUNTIF(C2:C7,4) |
- Write out the values first: 2, 5, 3, 4, 6, 4.
Show the completed table
| Formula | Result |
|---|---|
| =SUM(C2:C7) | 24 |
| =AVERAGE(C2:C7) | 4 |
| =MAX(C2:C7) | 6 |
| =COUNTIF(C2:C7,4) | 2 |
Apply it
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.
One box in a spreadsheet grid, named by its column letter and row number, such as B2.
A picture of data, such as a bar chart, line chart or pie chart, that makes comparisons and patterns quicker to see than a table of numbers.
An instruction typed into a spreadsheet cell, starting with =, that calculates a result from other cells, such as =SUM(C2:C13).
A program that stores data in a grid of rows and columns and can calculate with it using formulas.
A setting that changes how adventurous a model’s choice of the next token is: low means it nearly always picks the most likely token, high spreads its choices.
Your progress on this lesson
Saved in this browser only. It is not sent anywhere, does not follow you to another device, and is lost if you clear this site’s data.
This browser is not allowing the site to save progress, so it will be forgotten when you leave the page.