Handling missing values

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

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

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

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

5. A column of 8 rows has 2 blank cells. What does =COUNT(B2:B9) return?

A 6

B 8

Match the Pairs

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

Left column		Right column
1=COUNTBLANK(B2:B9)		ACOUNT 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.
2=AVERAGE(B2:B9)		BIgnores empty cells, so the mean is worked out from fewer values than there are rows
3Counting only the cells that hold a value shows how many rows have usable data, and blanks are left out		CCounts the empty cells in the range so you can see how many missing values need fixing
4A blank filled in from a reliable source stops the missing value from distorting results		DA 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.
5Fill every blank in Maths with 0 so AVERAGE works		EB5 = 72 (copied from the school records)
6Assume there are no blanks because =COUNT(B2:B9) shows 7		F=COUNT(B2:B9)

Definitions

missing value

Fill in the Blanks

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

=__________(B2:B9)

=_____(B2:B9)

=IF(_______(B2),"Missing",B2)

=IF(B2="",_______(A2,F:F,G:G),B2)

=COUNTIF(B2:B9,"__")

Spot the Error

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

Question 1

A Click any cell in the data

B Data > Sort, choose Blanks

C Delete the blank rows

Question 2

A =COUNTBLANK(B2:B9)

B =COUNT(B2:B9)

C =SUM(B2:B9)/ROWS(B2:B9)

Question 3

A Filter column C for Blanks

B Guess the value and type it in

C Type it into the blank cell

Question 4

A =IF(B2=0,"Missing",B2)

B =IF(B3="","Missing",B3)

C =IF(B4="","Missing",B4)

Question 5

A B11: =COUNT(B2:B9)

B B12: =COUNTBLANK(B2:B9)

C B13: =AVERAGE(B2:B9)

D B14: =B11-B12

Answer Key

Handling missing values

Section A: Quick Questions

A — Filter the column to show only blanks

A — Look up the real price in a reliable source such as the supplier list

B — Delete the row, or exclude it from the calculation

B — 70, because AVERAGE ignores the blank cell

A — 6

Section B: 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.

Section C: Definitions

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.

Section D: Fill in the Blanks

=COUNTBLANK(B2:B9)

=COUNT(B2:B9)

=IF(ISBLANK(B2),"Missing",B2)

=IF(B2="",XLOOKUP(A2,F:F,G:G),B2)

=COUNTIF(B2:B9,"<>")

Section E: Spot the Error

B — Sorting only reorders the rows. To show just the empty cells you must use Data > Filter and pick Blanks.

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.

B — A blank should be filled from a reliable source, such as the original records, not from a guess. Guessing adds unreliable data.

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).

D — The number of rows checked is the filled cells plus the blank cells, so add them: =B11+B12. Subtracting gives the wrong total.