← VCE Data Analytics U3O2

Counting coded responses to make quantitative data

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.

Part of: VCE Data Analytics - U3O2 - Analysis and Design — Module 64

Understanding Counting coded responses to make quantitative data in VCE Data Analytics U3O2

What it does: COUNTIF with the code as the criterion returns the frequency of that code. Adding all code frequencies gives the total, which is the denominator when calculating percentages. Frequency is the number of responses given a particular code, so 'Cost' has a frequency of 12. 40 is the total number of responses. Counting how many responses carry each code produces frequencies. These are numeric, so they can be shown in a chart. 10 ÷ 40 × 100 = 25%. The value 10 is the frequency, not the percentage, because the total is not 100. COUNTIF counts the cells that match a condition. SUM adds numbers, so it cannot count text codes.

Common mistakes: 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"). 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.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Counting coded responses to make quantitative data to a class →

This module's key terms are also in a printable crossword (Modules 32–75) and word search (Modules 3–146).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
A survey has 40 open-ended responses. 12 were coded 'Cost'. What is the frequency of the code 'Cost'?
1240
Matches
=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.
Applying
Meanings
Term
frequency
Meaning
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.
Practice
=COUNTIF(B2:B9,"Tech")/COUNTA(B2:B9)*100
Testing
Gaps
=(B2:B21,"Positive")
Check Answer
Spots
1E2: =COUNT(B2:B31,"Price")
2E3: =COUNTIF(B2:B31,"Staff")

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Coelacanth' badge — Living Fossil, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. A survey has 40 open-ended responses. 12 were coded 'Cost'. What is the frequency of the code 'Cost'?
A. 12 ✓
B. 40
Frequency is the number of responses given a particular code, so 'Cost' has a frequency of 12. 40 is the total number of responses.
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
Counting how many responses carry each code produces frequencies. These are numeric, so they can be shown in a chart.
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 ✓
Divide the code's frequency by the total number of responses, then multiply by 100. For example, 10 ÷ 40 × 100 = 25%.
4. 10 of 40 responses were coded 'Speed'. What percentage of responses is this?
A. 25% ✓
B. 10%
10 ÷ 40 × 100 = 25%. The value 10 is the frequency, not the percentage, because the total is not 100.
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 ✓
A frequency table lists each code once with its count, which gives you the data needed for a chart. Scattered comments cannot be charted directly.

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.

Key Term

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.

Fill in the Blanks

1. =COUNTIF(B2:B21,"Positive")
Hint: Type the function that counts how many cells in a range match a code.
2. =D2/COUNTA(B2:B21)
Hint: Type the function that counts all non-empty responses, giving the total to divide by.
3. =D2/SUM(D$2:D$4)
Hint: Type the operator that divides one code's frequency by the total of all frequencies.
4. =COUNTIF(B$2:B$21,A2)
Hint: Type the cell reference holding the code label, so the formula counts that code.
5. =D2/COUNTA(B$2:B$21)*100
Hint: Type the number that turns a proportion into a percentage.

Spot the Error

Question 1
A E2: =COUNT(B2:B31,"Price")
B E3: =COUNTIF(B2:B31,"Staff")
C E4: =COUNTIF(B2:B31,"Wait")
Line 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").
Question 2
A B2: =COUNTIF(A2:A21,"Price")
B B3: =COUNTA(A2:A21)
C C2: =B3/B2*100
Line 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.
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
Line C — The ← arrow assigns a value, so it can't test whether the code matches. A comparison is needed: IF code = "Price" THEN.
Question 4
A B2: =COUNTIF(A2:A21,"Staff")
B B3: =COUNTIF(A2:A21,Wait)
C B4: =SUM(B2:B3)
Line 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").
Question 5
A SET total TO 12
B SET staffCount TO 3
C SET pct TO staffCount / total * 100
D DISPLAY total & "%"
Line D — This displays the total number of responses (12%), not the percentage for the Staff code. It should display pct: DISPLAY pct & "%".

Student with a class code? Join your class →