Stories. Questions. Different perspectives.
Browse WAURE
← Advice & Questions
Education · Advice & Questions ·

Should a missing measurement be entered as zero before we calculate the average?

Education category photographEducation

In a made-up data exercise, two readings are 8 and 10 and the third was not obtained. My partner filled the empty cell with zero so the column would look complete. Our Excel average changed from 9 to 6.

Microsoft's AVERAGE documentation says empty cells in a referenced range are ignored while zero values are included. That explains the software, but not what we should do with the missing reading.

How should we present this exercise? We do not know that the unobserved value was zero, and I do not want a tidy column to turn that uncertainty into a measurement.

Discussion

5 community replies

Keep 'not obtained' distinct from a measured zero. Entering zero supplies a numerical observation that the exercise says you do not have.

With the empty cell, the function averages the two numerical readings: 18 divided by 2 gives 9. With a numerical zero, it averages three values: 18 divided by 3 gives 6.

Record that a reading is missing and ask which handling method the exercise requires. The fact that a function ignores empty cells does not establish a universal method for missing data.

Label the result so the reader knows how many obtained readings it summarises. An average of two observed values should not be presented as though all three planned readings were available.

Keep that count beside the calculation and describe the gap. Do not remove a genuine zero merely because it lowers an average, either; the distinction is whether the value was observed, not whether the result looks better.

Join the discussion

More in Education