Counting coded responses to make quantitative data

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A survey has 40 open-ended responses. 12 were coded 'Cost'. What is the frequency of the code 'Cost'?

A 12

B 40

2. Which describes what counting coded responses achieves?

A It turns qualitative data into quantitative counts that can be charted

B It turns numbers into free-text comments

3. Which is the correct way to find the percentage of responses with the code 'Speed'?

A Speed frequency × total responses ÷ 100

B Speed frequency ÷ total responses × 100

4. 10 of 40 responses were coded 'Speed'. What percentage of responses is this?

A 25%

B 10%

5. Where should you record the frequency for each code so it can be charted?

A Next to each original response as extra comments

B In a frequency table with one row per code

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1=COUNTIF(B2:B31,"Cost")		A=SUM(C2:C6)
2=C2/SUM(C$2:C$6)		BDivides one code's frequency by the total of all frequencies to give the percentage of responses
3Count how many responses were given the code Safety in a coded column		CCounts how many coded responses in B2:B31 have the code Cost, giving that code's frequency
4Find the total number of coded responses so frequencies can be turned into percentages		DThe code Cost is text, so it needs quotation marks. Without them the formula looks for a named range called Cost and gives an error.
5=COUNTIF(B2:B31,Cost)		E=COUNTIF(B2:B31,"Safety")
6=C2/C3		FThis divides one code's frequency by another code's frequency instead of by the total number of responses, so it is not a percentage of all responses.

Definitions

frequency

Fill in the Blanks

Complete each line of code by filling in the missing parts.

=_______(B2:B21,"Positive")

=D2/______(B2:B21)

=D2_SUM(D$2:D$4)

=COUNTIF(B$2:B$21,__)

=D2/COUNTA(B$2:B$21)*___

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A E2: =COUNT(B2:B31,"Price")

B E3: =COUNTIF(B2:B31,"Staff")

C E4: =COUNTIF(B2:B31,"Wait")

Question 2

A B2: =COUNTIF(A2:A21,"Price")

B B3: =COUNTA(A2:A21)

C C2: =B3/B2*100

Question 3

A SET price TO 0

B FOR EACH code IN responses

C IF code ← "Price" THEN

D SET price TO price + 1

E END IF

F END FOR

Question 4

A B2: =COUNTIF(A2:A21,"Staff")

B B3: =COUNTIF(A2:A21,Wait)

C B4: =SUM(B2:B3)

Question 5

A SET total TO 12

B SET staffCount TO 3

C SET pct TO staffCount / total * 100

D DISPLAY total & "%"

Answer Key

Counting coded responses to make quantitative data

Section A: Quick Questions

A — 12

A — It turns qualitative data into quantitative counts that can be charted

B — Speed frequency ÷ total responses × 100

A — 25%

B — In a frequency table with one row per code

Section B: Match the Pairs

=COUNTIF(B2:B31,"Cost") → Counts how many coded responses in B2:B31 have the code Cost, giving that code's frequency

=C2/SUM(C$2:C$6) → Divides one code's frequency by the total of all frequencies to give the percentage of responses

Count how many responses were given the code Safety in a coded column → =COUNTIF(B2:B31,"Safety")

Find the total number of coded responses so frequencies can be turned into percentages → =SUM(C2:C6)

=COUNTIF(B2:B31,Cost) → The code Cost is text, so it needs quotation marks. Without them the formula looks for a named range called Cost and gives an error.

=C2/C3 → This divides one code's frequency by another code's frequency instead of by the total number of responses, so it is not a percentage of all responses.

Section C: Definitions

frequency — The number of responses given a particular code. Counting each code turns qualitative data into counts you can chart; dividing by the total gives a percentage.

Section D: Fill in the Blanks

=COUNTIF(B2:B21,"Positive")

=D2/COUNTA(B2:B21)

=D2/SUM(D$2:D$4)

=COUNTIF(B$2:B$21,A2)

=D2/COUNTA(B$2:B$21)*100

Section E: Spot the Error

A — COUNT only counts numeric cells, so it ignores text codes and returns 0. To count how many responses have the code "Price", use COUNTIF(B2:B31,"Price").

C — A percentage is the code's frequency divided by the total number of responses. The formula has them the wrong way round. It should be =B2/B3*100.

C — The ← arrow assigns a value, so it can't test whether the code matches. A comparison is needed: IF code = "Price" THEN.

B — The criteria text must be in quotation marks, otherwise the spreadsheet treats Wait as a name and gives an error. It should be =COUNTIF(A2:A21,"Wait").

D — This displays the total number of responses (12%), not the percentage for the Staff code. It should display pct: DISPLAY pct & "%".