Entering survey responses consistently
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. A survey has 3 people. How should the responses be laid out in the spreadsheet?
2. Which way of entering a Yes answer keeps the data consistent?
3. A student answers 'Yes, but only on weekends'. What is the best way to enter it in a Yes/No column?
4. A respondent skipped the question 'Do you own a pet?'. What should go in the cell?
5. Which cell entry follows the 'one answer per cell' rule?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1Yes | AThe same answer is typed four different ways. It should be Yes in every cell. | |
| 2(blank cell) | BThe one agreed spelling for a yes answer, typed the same way in every row | |
| 3Each survey answer goes in its own cell, so a respondent's answers are spread across the columns of their row | C=COUNTIF(B2:B20,"Yes") | |
| 4Count the Yes answers in a column because every Yes was typed with the same spelling | DWhat to leave when a respondent gave no answer to a question | |
| 5B2: Yes | B3: yes | B4: Y | B5: yeah | ESeveral answers are crammed into one cell. Each answer needs its own cell: Mia in A2, Yes in B2, 13 in C2. | |
| 6A2: Mia, Yes, 13 (all typed in one cell) | FA2: Mia | B2: Yes | C2: 13 |
C
Definitions
consistent data entry
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
=COUNTIF(B2:B9,"___")
2.
=__________(B2:B9)
3.
=______(A2:A9)
4.
=IF(B2_"Yes",1,0)
5.
Respondent 3 answer to Q1 goes in cell B_
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
Name,Pizza,Age
B
Ava,Yes,13
C
Ben,No,12
D
Cam,yeah,13
Question 2
A
Name,Likes sport and Age
B
Mia,Yes,12
C
Tom,No,13
Question 3
A
Zoe,Yes,12,Max,No,11
B
Kai,No,13
C
Eli,Yes,12
Question 4
A
Name,Pet,Age
B
Ben,no answer,12
C
Cal,Cat,12
Question 5
A
Name,Swims,Age
B
Ava,Yes,13
C
Ben,N,12
D
Cal,Yes,12
Answer Key
Entering survey responses consistently
Section A: Quick Questions
Q1
B — One row per respondent
Q2
A — Yes
Q3
B — Pick the matching option, Yes, and type only that in the cell
Q4
A — Leave the cell blank
Q5
A — Cell B2 holds Pizza and cell C2 holds Pasta
Section B: Match the Pairs
Q1
Yes → The one agreed spelling for a yes answer, typed the same way in every row
Q2
(blank cell) → What to leave when a respondent gave no answer to a question
Q3
Each survey answer goes in its own cell, so a respondent's answers are spread across the columns of their row → A2: Mia | B2: Yes | C2: 13
Q4
Count the Yes answers in a column because every Yes was typed with the same spelling → =COUNTIF(B2:B20,"Yes")
Q5
B2: Yes | B3: yes | B4: Y | B5: yeah → The same answer is typed four different ways. It should be Yes in every cell.
Q6
A2: Mia, Yes, 13 (all typed in one cell) → Several answers are crammed into one cell. Each answer needs its own cell: Mia in A2, Yes in B2, 13 in C2.
Section C: Definitions
Q1
consistent data entry — Typing each survey answer in its own cell, one row per person, using the same spelling and format every time (Yes, not yes or Y), and leaving the cell blank if there is no answer.Section D: Fill in the Blanks
Q1
=COUNTIF(B2:B9,"Yes")Q2
=COUNTBLANK(B2:B9)Q3
=COUNTA(A2:A9)Q4
=IF(B2="Yes",1,0)Q5
Respondent 3 answer to Q1 goes in cell B4Section E: Spot the Error
Q1
D — Every Yes/No answer must be typed the same way. 'yeah' does not match 'Yes', so the line should be Cam,Yes,13.
Q2
A — Each heading needs its own cell. 'Likes sport' and 'Age' are squashed into one cell. It should be Name,Likes sport,Age.
Q3
A — Use one row per respondent. This line holds two people (Zoe and Max). Zoe's answers belong on their own row: Zoe,Yes,12.
Q4
B — When a person gives no answer, leave the cell blank. Typing 'no answer' adds text that is not real data. It should be Ben,,12.
Q5
C — Use the full word No every time, not the short form N. Mixed spellings are counted as different answers. It should be Ben,No,12.