Blank, Zero and "NA" Are Three Different Things

The distinction that changes your results

A truly empty cell means we do not know. A zero means we know, and it is zero. The text NA means somebody typed a note where a number belongs. Excel treats all three differently, and confusing them is the most common way a mean ends up wrong without anyone noticing — because the number that comes out is perfectly reasonable, just not the one you asked for.

What each function does with them

• Empty: skipped by AVERAGE, COUNT, SUM, MIN, MAX. It changes the denominator, not the total, and it counts in COUNTBLANK.
• Zero: a full observation. It is added into the total and it increases the count, so it pulls the mean toward zero. MIN will happily report it as your smallest value.
• Text such as NA or n/a: skipped by the arithmetic exactly like text numbers, counted by COUNTA, and it makes the column non-numeric for sorting and lookups.
• The error #N/A: not skipped. It propagates: any SUM or AVERAGE touching it returns #N/A. That is loud, and loud is better than silent.

Which one is right depends on the sensor, not on the spreadsheet

A shop that was closed on Sunday sold zero units — that is a zero, and dropping it would overstate the daily average. A thermometer that failed on Sunday recorded nothing — that is a blank, and writing zero would drag the mean down by inventing a very cold day. Nobody can recover this from the file afterwards; it has to be recorded correctly at the point of collection, which is why a column of measurements should never be filled in with zeros to make it look complete.

Making missingness visible

Before summarising any column, count what is not there. Two cells are enough:

=COUNTBLANK(B2:B200)                  ' how many are missing
=COUNTBLANK(B2:B200)/ROWS(B2:B200)    ' as a share of the column
=COUNTA(B2:B200)-COUNT(B2:B200)       ' text placeholders such as NA

A column missing 2% of its values is usually reportable with a note. A column missing 30% is a different dataset from the one you think you have, and the honest move is to say so rather than to average what remains and hope the missing values were like the present ones.

The assumption you are making when you ignore blanks

AVERAGE skipping blanks is not neutral — it assumes the missing values would have looked like the ones you have. Statisticians call that missing completely at random. It fails exactly when it matters: sensors fail in extreme cold, customers skip the income question when the answer is high, a shop's till breaks on its busiest day. If the missingness is related to the value itself, every summary you compute is biased, and no formula will tell you. Substituting the mean for missing values does not fix this; it hides it, and it shrinks the variance on top, which makes every later confidence interval too narrow.