← VCE Data Analytics U3O1

Handling missing values

An empty cell where data should be. Find it with a filter for blanks, then delete the row, fill it from a reliable source or exclude it so AVERAGE and COUNT are not distorted.

Part of: VCE Data Analytics - U3O1 - Data Analytics — Module 76, ACARA Digital Technologies Years 7–8 — Acquiring and Storing Data — Module 57, ACARA Digital Technologies Years 9–10 — Acquiring and Validating Data — Module 101, ACARA Digital Technologies Years 9–10 — Analysing and Visualising Data — Module 5, QCE Digital Solutions Unit 3 — Real-World Problems and Solution Requirements — Module 86, QCE Digital Solutions Unit 4 — Complex Digital Data Exchange Problems and Solution Requirements — Module 69

Understanding Handling missing values in VCE Data Analytics U3O1

What it does: COUNT only counts cells with numbers, so a blank is not counted and the total is lower than the number of rows. Typing in a value taken from a reliable source is one way to fix a missing value so AVERAGE and COUNT are accurate. AVERAGE skips empty cells, so (80+60+70)/3 = 70. If someone types a 0 into the blank, the average falls to 52.5. Blanks do not break the file, but they can lead to misleading counts and averages if not handled. Finding the blanks is only the first step. You then need to fix, fill or exclude them so the results are not distorted.

Common mistakes: Sorting only reorders the rows. To show just the empty cells you must use Data > Filter and pick Blanks. ROWS counts every row, including the ones with a blank, so the mean is too low. Divide by COUNT, which counts only the cells that hold numbers.

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 Handling missing values to a class →

This module's key terms are also in a printable crossword (Modules 50–91) and word search (Modules 17–91).

Take this worksheet further — free with Cloodit

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

Learning
Duo
A student wants to find every missing value in the Maths column of a spreadsheet. Which approach works?
Sort the column and hope the blanks disappearFilter the column to show only blanks
Matches
Fill every blank in Maths with 0 so AVERAGE works
↓
A made-up 0 is not a real value, so it drags the average down and distorts the result. Fill from a reliable source or exclude the row instead.
Applying
Meanings
Term
missing value
Meaning
An empty cell where data should be. Find it with a filter for blanks, then delete the row, fill it from a reliable source or exclude it so AVERAGE and COUNT are not distorted.
Practice
=IF(COUNT(B2:D2)=3,AVERAGE(B2:D2),"Check")
Testing
Gaps
=(B2:B9)
Check Answer
Spots
1Click any cell in the data
2Data > Sort, choose Blanks

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

The 'Horned Lizard' badge — Bug Squasher, 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 student wants to find every missing value in the Maths column of a spreadsheet. Which approach works?
A. Filter the column to show only blanks ✓
B. Sort the column and hope the blanks disappear
A filter for blanks shows only the empty cells, so you can see exactly which rows need fixing.
2. A cell in the Price column is empty. What is a sensible way to FILL it?
A. Look up the real price in a reliable source such as the supplier list ✓
B. Type in a random price that looks about right
Filling a gap should use a reliable source. A guessed value can distort results and is not authentic data.
3. Ten rows hold test scores. One row has a blank score and no other usable data. Which is a valid way to handle it?
A. Leave it blank and assume the spreadsheet will treat it as the average
B. Delete the row, or exclude it from the calculation ✓
A missing value must be deleted, filled or excluded. A spreadsheet does not replace a blank with the average.
4. A column holds the marks 80, 60, 70 and one blank cell. What does =AVERAGE(B2:B5) return?
A. 52.5, because the blank is counted as 0
B. 70, because AVERAGE ignores the blank cell ✓
AVERAGE skips empty cells, so (80+60+70)/3 = 70. If someone types a 0 into the blank, the average falls to 52.5.
5. A column of 8 rows has 2 blank cells. What does =COUNT(B2:B9) return?
A. 6 ✓
B. 8
COUNT only counts cells that contain numbers. Blank cells are not counted, so the count is lower than the number of rows.

Match the Pairs

=COUNTBLANK(B2:B9) → Counts the empty cells in the range so you can see how many missing values need fixing
=AVERAGE(B2:B9) → Ignores empty cells, so the mean is worked out from fewer values than there are rows
Counting only the cells that hold a value shows how many rows have usable data, and blanks are left out → =COUNT(B2:B9)
A blank filled in from a reliable source stops the missing value from distorting results → B5 = 72 (copied from the school records)
Fill every blank in Maths with 0 so AVERAGE works → A made-up 0 is not a real value, so it drags the average down and distorts the result. Fill from a reliable source or exclude the row instead.
Assume there are no blanks because =COUNT(B2:B9) shows 7 → COUNT skips blank cells, so a lower total can show that values are missing. The blanks should be found with a filter or COUNTBLANK, then fixed.

Key Term

missing value
An empty cell where data should be. Find it with a filter for blanks, then delete the row, fill it from a reliable source or exclude it so AVERAGE and COUNT are not distorted.

Fill in the Blanks

1. =COUNTBLANK(B2:B9)
Hint: Type the function that counts the empty cells in a range.
2. =COUNT(B2:B9)
Hint: Type the function that counts only cells holding numbers, so blanks are left out of the total.
3. =IF(ISBLANK(B2),"Missing",B2)
Hint: Type the function that tests whether a cell is empty.
4. =IF(B2="",XLOOKUP(A2,F:F,G:G),B2)
Hint: Type the lookup function that fills a blank from a reliable source table.
5. =COUNTIF(B2:B9,"<>")
Hint: Type the comparison operator meaning 'not equal to'. With nothing after it, the criteria counts only cells that are not empty.

Spot the Error

Question 1
A Click any cell in the data
B Data > Sort, choose Blanks
C Delete the blank rows
Line B — Sorting only reorders the rows. To show just the empty cells you must use Data > Filter and pick Blanks.
Question 2
A =COUNTBLANK(B2:B9)
B =COUNT(B2:B9)
C =SUM(B2:B9)/ROWS(B2:B9)
Line C — ROWS counts every row, including the ones with a blank, so the mean is too low. Divide by COUNT, which counts only the cells that hold numbers.
Question 3
A Filter column C for Blanks
B Guess the value and type it in
C Type it into the blank cell
Line B — A blank should be filled from a reliable source, such as the original records, not from a guess. Guessing adds unreliable data.
Question 4
A =IF(B2=0,"Missing",B2)
B =IF(B3="","Missing",B3)
C =IF(B4="","Missing",B4)
Line A — B2=0 tests for the number zero, which is a real value, not a blank. To test for an empty cell use B2="" (or ISBLANK).
Question 5
A B11: =COUNT(B2:B9)
B B12: =COUNTBLANK(B2:B9)
C B13: =AVERAGE(B2:B9)
D B14: =B11-B12
Line D — The number of rows checked is the filled cells plus the blank cells, so add them: =B11+B12. Subtracting gives the wrong total.

Student with a class code? Join your class →