Study Guide Instructor view
Collecting and Analysing Data
Teaching notes for Lesson 7.4, with controls for presenting it.
Keep this page on your own screen. Share only the lesson window. The lesson window holds no teaching notes, so sharing it cannot show them.
Presenting
- Open the lesson in its own window and share that window, not this one.
- Use Next and Previous here, or the lesson’s own, to move through the lesson. Next reveals one thing at a time: the next idea, or the answer to the question on screen, then the next step. Previous takes the last reveal back. The line below says what is on screen and what Next will show.
- Reveal hints and answers when the class is ready, and reset a demonstration before using it again.
The controls need JavaScript and a browser that supports BroadcastChannel. Without them, open the lesson in presentation mode and use its own controls.
The two windows talk to each other only inside this browser, on this device. Nothing is sent to a server, and no account is needed. A second device (a phone or another tablet) cannot follow the lesson window yet.
Now on the lesson window
- On screen
- Next teaching point
Guidance for this point
Lesson plan
- Level
- Beginner
- Time
- About 75 minutes; the steps below add up to 73
A small project for a known user: pupils plan, collect, organise, chart, conclude and present, using a fictional PE survey. They check formulas by hand so that they understand what a spreadsheet does, and learn to draw only the conclusions that the data supports. The lesson teaches the thinking behind a project and works with any spreadsheet program.
Objectives
- Plan a small project around the needs of a known user, choosing which applications to combine
- Write survey questions that are clear and fair
- Organise survey data in a spreadsheet and work out the result of =SUM, =AVERAGE, =MAX and =COUNTIF formulas
- Choose a suitable chart and explain what makes a chart honest
- Draw conclusions that the data supports, and present them to the user
- Evaluate the project against the user’s needs
Before the lesson
- Paper copies or a screen of the data table; optional access to a spreadsheet program so pupils can check results.
- Calculators allowed for the addition if wanted.
- Mini whiteboards for predictions.
Steps
Introduction Opening
On the lesson window now
On screen: the lesson’s title, module and number; Next adds its objectives and key terms, as the lesson page lists them; Next again begins the first step.
Objectives on screen
- Plan a small project around the needs of a known user, choosing which applications to combine
- Write survey questions that are clear and fair
- Organise survey data in a spreadsheet and work out the result of =SUM, =AVERAGE, =MAX and =COUNTIF formulas
- Choose a suitable chart and explain what makes a chart honest
- Draw conclusions that the data supports, and present them to the user
- Evaluate the project against the user’s needs
Purpose: A small project for a known user: pupils plan, collect, organise, chart, conclude and present, using a fictional PE survey. They check formulas by hand so that they understand what a spreadsheet does, and learn to draw only the conclusions that the data supports. The lesson teaches the thinking behind a project and works with any spreadsheet program.
1. Someone needs an answer Think 6 min
On the lesson window now
Purpose: Start from a real need: a user with a question, a format and a deadline.
Ask
- What do you need to find out from Ms Okafor?
- What do you need to find out from pupils?
Responses to expect
- Which clubs, one page, by Friday
- Club choice and whether they could come
Misconceptions to challenge
- A learner may believe: “Start by collecting as much data as possible.” Respond: Ask what question the data must answer; unneeded data wastes time.
Checking understanding: Listen for the user’s question and format being named.
Depending on the answers
- If pupils only list survey questions: ask what Ms Okafor has to decide and in what form.
English as an additional language
- Check “user” means the person the work is for.
In the lesson window
- 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?
2. Planning and choosing applications Discover 7 min
On the lesson window now
Purpose: Choosing and combining applications for different jobs.
Ask
- Which program for each job?
- Why a spreadsheet for the calculating?
Responses to expect
- Form, spreadsheet, chart, document
- It calculates with data using formulas
Misconceptions to challenge
- A learner may believe: “One program should do everything.” Respond: Each program has strengths; projects combine them.
Checking understanding: The choose-application question.
Depending on the answers
- If pupils choose the word processor: ask which program can add up a column of numbers for you.
In the lesson window
- 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?
- 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?
3. Asking good questions Explain 8 min
On the lesson window now
Purpose: Pupils spot flaws in survey questions: leading, two-in-one, no clear choices.
Ask
- Which question would you keep?
- What is wrong with Question 1?
Responses to expect
- Question 2
- It pushes towards football
Misconceptions to challenge
- A learner may believe: “A friendly or enthusiastic question is a good question.” Respond: It can push the answer; a neutral wording is fairer.
Checking understanding: The survey-flaw question tests the leading question in a new wording.
Depending on the answers
- If pupils choose the “two days” option: ask which words suggest the answer the writer wants.
Support
- Ask pupils to rewrite Question 1 neutrally.
In the lesson window
- Think it through: Which of the three questions would you keep, and what is wrong with each of the others?
- Check your understanding: “Don’t you agree that a Thursday club would be better than a Monday club?” What is the main problem?
4. Organising data in a spreadsheet Experiment 14 min
On the lesson window now
Purpose: Layout one row per pupil, cell names and formulas, with hand checks of the results.
Ask
- How would you lay out the data?
- What does =COUNTIF(B2:B13, "Football") do?
Responses to expect
- A row per pupil and a column per question
- It counts the cells that say Football
Misconceptions to challenge
- A learner may believe: “A formula is a magic answer.” Respond: A formula is an instruction; check it by working it out by hand.
- A learner may believe: “SUM can add words.” Respond: SUM adds numbers; counting words needs COUNTIF.
On the board or with the pen
- Write C2:C5 and circle the four cells; add them step by step.
Checking understanding: The formula-results table and the which-formula question.
Depending on the answers
- If pupils get the SUM wrong: have them write out the cells and add in pairs.
- If pupils choose the AVERAGE option for counting: ask what AVERAGE does and whether words can be averaged.
Support
- Allow a calculator; mark the rows with a finger as you add.
Stretch
- Write the formula that counts the pupils who chose Chess.
In the lesson window
- 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?
- Check your understanding: Ms Okafor asks how many pupils chose Dance. Which formula answers that?
5. Choosing a chart Predict 7 min
On the lesson window now
Purpose: Match the chart to the question: bars to compare, lines for change, pies for parts of a whole.
Ask
- What picture would show the clubs?
- Why a line chart for temperature?
Responses to expect
- Four bars
- Because it shows change over time
Misconceptions to challenge
- A learner may believe: “A pie chart is best for everything.” Respond: A pie suits a few parts of one whole; comparisons need bars.
Checking understanding: The chart-choice question.
Depending on the answers
- If pupils choose the pie for the days: ask what the pupil wants to see across the days.
In the lesson window
- 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?
- 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?
6. Is the chart honest? Apply 7 min
On the lesson window now
Purpose: A chart axis that does not start at zero can mislead; pupils quantify the distortion.
Ask
- How many times taller does Football look?
- How would you fix the chart?
Responses to expect
- Three times, but really twice
- Start the axis at 0 and label it
Misconceptions to challenge
- A learner may believe: “A chart is true because it uses real numbers.” Respond: The scale can still mislead; check the axis.
Checking understanding: The misleading-axis question applies the same idea to new numbers.
Depending on the answers
- If pupils choose hiding the axis numbers: ask how a reader would then know the scale.
In the lesson window
- 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?
- 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?
7. What can you conclude? Explain 7 min
On the lesson window now
Purpose: Conclude only what the data supports: for whom and how many.
Ask
- What does the data support?
- What can it not tell Ms Okafor?
Responses to expect
- Football and Dance joint most popular
- Anything about the whole school
Misconceptions to challenge
- A learner may believe: “The first item in a list is the most popular.” Respond: Order is not a result; check the counts.
- A learner may believe: “A small class stands for the whole school.” Respond: Say who was asked and how many.
Checking understanding: The conclusion-claim question.
Depending on the answers
- If pupils choose the whole-school statement: ask how many were asked and from which class.
In the lesson window
- Think it through: Write one conclusion for Ms Okafor that the data supports, and one thing the data cannot tell her.
- Check your understanding: Which statement does this data support?
8. Present it, then judge it Apply 7 min
On the lesson window now
Purpose: Present the answer first for a busy user; evaluate against what the user asked.
Ask
- What goes at the top?
- How will you know whether it worked?
Responses to expect
- The answer to her question
- Ask her whether it helps her decide
Misconceptions to challenge
- A learner may believe: “Hard work means a good result.” Respond: The test is whether it meets the user’s needs.
Checking understanding: The evaluate-against-needs question.
Depending on the answers
- If pupils judge by decoration: ask what Ms Okafor must do with the page.
In the lesson window
- 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?
- Check your understanding: Which is the best way to evaluate the finished one-page summary?
9. Try a new project yourself Consolidate 10 min
On the lesson window now
Purpose: Independent work on a new data set, a chart choice with honest notes, and an explanation of the whole process.
Ask
- What does each formula give?
- Which chart and which note?
Responses to expect
- 24, 4, 6 and 2
- A bar chart from 0, noting six pupils
Misconceptions to challenge
- A learner may believe: “COUNTIF(C2:C7,4) adds the fours.” Respond: COUNTIF counts the cells equal to 4; two cells match.
Checking understanding: The library table and the final chart question show who can transfer the ideas.
Depending on the answers
- If pupils get the average wrong: check the total first, then divide by six.
Stretch
- Design three survey questions for Mr Singh that are fair and countable.
In the lesson window
- 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?
- 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.
Lesson recap Closing
On the lesson window now
On screen: the lesson’s objectives again; Next adds the closing question, which asks the class to explain each one in their own words without looking back. It says nothing about what anyone has learnt: use the answers to decide what to revisit.
Points to listen for
- Begin with the user’s question and plan the data and applications.
- Ask neutral, one-thing questions with fixed choices.
- A spreadsheet holds data in cells; formulas such as SUM, AVERAGE, MAX and COUNTIF give results you can check by hand.
- Choose and label an honest chart, conclude only what the data shows, and evaluate against the user’s needs.
Consolidation
- Begin with the user’s question and plan the data and applications.
- Ask neutral, one-thing questions with fixed choices.
- A spreadsheet holds data in cells; formulas such as SUM, AVERAGE, MAX and COUNTIF give results you can check by hand.
- Choose and label an honest chart, conclude only what the data shows, and evaluate against the user’s needs.