Before you summarise: missing and impossible values

By the end of this lesson you can check a column before you summarise it: count the numbers, the blanks and the text with COUNT, COUNTA and COUNTBLANK, find impossible values with COUNTIF, and explain why you must never fill a gap or delete a value without saying so.

Why it matters

A spreadsheet never warns you. AVERAGE quietly skips a blank cell and the word "absent", and it happily includes a mark of 130 out of 100. The answer looks precise, but it is built on rubbish. Professional analysts spend a lot of their time checking data before any calculation: if the data is wrong, every number after it is wrong too.

The idea in plain words

Imagine a teacher handing you eight mark sheets. Six have a mark. One is empty. One says "absent". And one says 130, even though the test was out of 100.

What AVERAGE does with each cell. Made-up numbers.

Before you summarise, ask four questions about every column:

1. How many cells are there?
2. How many hold a number?
3. How many are empty or hold text instead?
4. Is any number impossible (a mark above 100, a negative age, a wait of 9,000 minutes)?

Missing value: a cell where the value was not recorded. It may be blank, or hold a marker such as "NA" (not available) or "absent".

Impossible value: a value that cannot be true, usually a typing or measuring mistake: a mark of 130 out of 100, a mass of −5 kg.

See it done

Data, part 1: the eight made-up marks above, typed into cells A2 to A9 of a new sheet: 56, 71, (blank), 64, 130, 48, absent, 82. Leave A4 empty, and type the word absent in A8.

1. Cells holding a number: =COUNT(A2:A9) gives 6.
2. Cells that are not empty: =COUNTA(A2:A9) gives 7 (the six numbers plus the word "absent").
3. Empty cells: =COUNTBLANK(A2:A9) gives 1.
4. Impossible marks: =COUNTIF(A2:A9, ">"&100) gives 1.
5. What AVERAGE does: =AVERAGE(A2:A9) gives 75.2. It used all 6 numbers, including the impossible 130.
6. Only the possible marks: =AVERAGEIFS(A2:A9, A2:A9, "<="&100) gives 64.2, from 5 marks. AVERAGEIFS averages only the cells that pass a test. You give it three things: the column to average, a column to test, and the test. Here the column to average and the column to test are the same one, A2:A9, and the test "<="&100 keeps only marks of 100 or below (the & joins the <= sign to the number 100, as you saw with COUNTIF in lesson 2).

One impossible value moves the average a long way. Made-up numbers.

Data, part 2: penguins.csv (real). Column E is flipper_length_mm, column G is sex, rows 2 to 345.

1. How many flippers were measured? =COUNT(E2:E345) gives 342, but =COUNTA(E2:E345) gives 344. The difference, =COUNTA(E2:E345)-COUNT(E2:E345), is 2: 2 cells say "NA" because those penguins were not measured.
2. How many penguins have no recorded sex? =COUNTIF(G2:G345, "NA") gives 11.
3. What if you filled the gaps with 0? =AVERAGE(E2:E345) skips the "NA" cells and gives 200.92 mm. If someone typed 0 in those 2 cells, the average would become =SUM(E2:E345)/COUNTA(E2:E345) = 199.75 mm: wrong, because no penguin has a flipper of 0 mm.

Three rules for missing and impossible values.

1. Count them, and report them. "Flipper length was missing for 2 of 344 penguins."
2. Never fill a gap with 0 or a guess without saying so. Zero is a real value; it is not the same as "unknown".
3. Check an impossible value with the person who recorded it. Fix it if you can find the true value. If not, leave it out of the calculation and say that you did.

Your turn, with help

In penguins.csv, column F is body_mass_g. How many cells hold a number, and how many are missing?

Hints: =COUNT(F2:F345) counts the numbers. =COUNTA(F2:F345)-COUNT(F2:F345) counts the cells that hold something other than a number.

Show the answer

=COUNT(F2:F345) gives 342 and the difference gives 2: two penguins have no body mass. (They are the same two penguins with no flipper length: they were not measured at all.)

Your turn, alone

1. In lucia-classes-marks.csv (synthetic), column C is maths_mark, rows 2 to 61. How many marks are there, and how many are above 100 or below 0?
2. A colleague fills every "NA" in a column of body masses with 0 and then reports the average. Explain in one sentence what goes wrong.
3. Priya finds a waiting time of 9,000 minutes in her records. Write the three things she should do, in order.

Watch out

• Text that looks like a number. A number typed with a space or a letter (for example "45 min") is text: COUNT skips it and so does AVERAGE. If COUNT is smaller than you expect, look for numbers stored as text.
• Deleting rows to "clean" the data. If you delete a row with a missing value, you also delete everything else recorded about that case. Leave the row, and let the formulas skip the gap.
• Unusual is not impossible. A value can be unusual but real (Amara's festival day). An impossible value is a mistake. Module 2 shows how to flag unusual values; this lesson is about impossible ones.

Say it back

1. What does COUNTA(range) - COUNT(range) tell you?
2. Why is filling a missing value with 0 a mistake?
3. What should you do with an impossible value?

Show the answers

1. How many cells hold something that is not a number, such as "NA" or a word.
2. Zero is a real value. It pulls the average down (or up) as if it had been measured, when in fact the value is unknown.
3. Check it with the person who recorded it; fix it if the true value is known; otherwise leave it out of the calculation and say so.

What is next

Your columns are checked. Back to Amara's question about fruit names: you cannot add them up or put them in order of size. Lesson 6 brings the tool for that: the mode.

Formulas in this lesson were checked on 2026-10-11 against the official Google Sheets and Microsoft Excel help pages (COUNT, COUNTA, COUNTBLANK, COUNTIF, AVERAGEIFS), and every number was calculated by running the formulas in a spreadsheet program (LibreOffice Calc) and checked by a separate calculation. Menu steps are written for Google Sheets on the web and Excel for Microsoft 365.

---

Answers to Your turn, alone

Show the answers

1. =COUNT(C2:C61) gives 60. =COUNTIF(C2:C61, ">"&100) gives 0 and =COUNTIF(C2:C61, "<"&0) gives 0: no impossible marks.
2. The zeros are treated as real masses, so the average is pulled down, and it no longer describes the penguins that were weighed.
3. Count and report it; check it with whoever recorded it; fix it if the true value can be found, otherwise leave it out of the calculation and say so in the report.