AVERAGE in Excel: why a blank and a zero give different results

Keep missing observations separate from recorded zeroes

A workshop records the number of minutes a machine spent idle during four checks. The entries are 12, 0, an empty cell and 18. The zero means the check was completed and no idle time was observed. The empty cell means that check has no recorded result.

Put the check labels in A2:A5 and the readings in B2:B5. Leave B4 genuinely empty. This distinction matters before any formula is written: a spreadsheet cannot infer whether a missing entry should have been zero.

Calculate the mean of the recorded values

There are three numeric observations: 12, 0 and 18. Their sum is 30, so their arithmetic mean is 30 divided by 3, or 10 minutes. AVERAGE on this cell range includes zero and ignores the empty cell.

Each recorded check has equal weight in this calculation. This is not a mean weighted by machine runtime or production volume. If checks represent different amounts of work, decide whether an equal-weight mean answers your question.

=AVERAGE(B2:B5)

Show the observation count beside the result

The average alone does not reveal whether all four checks were recorded. COUNT returns 3 for this range. Reporting both 10 minutes and 3 recorded checks makes the missing observation easier to spot.

Use the same range in the two formulas. Counting a different column can give a misleading impression of completeness when a check has a label but no numeric reading.

=COUNT(B2:B5)

See what changes when the blank is filled with zero

Enter 0 in B4. The sum remains 30, but the count rises to 4, so the mean becomes 7.5 minutes. This is appropriate only if that check actually recorded zero idle time.

Do not enter zero merely to make the sheet look complete. You would be changing the meaning of the data and lowering the mean. Equally, do not delete a real zero because you want an average of only positive readings; that is a different question and should be labelled as such.

A text note does not supply a numeric observation

If B4 contains the note not recorded, AVERAGE over the range still uses 12, 0 and 18, producing 10. The note may explain the gap, but it does not create another numeric reading. A formula returning empty text also contributes no number.

Numbers imported as text need inspection too. A displayed 18 stored as text can be ignored in a range average. Check the source type and convert deliberately rather than assuming the displayed characters prove that the cell is numeric.

Handle the case where there are no readings

If every reading is empty, there is no arithmetic mean to calculate. AVERAGE returns #DIV/0!, because the count of numeric observations is zero. Returning 0 instead would falsely report a measured value.

The guarded formula below returns No readings when the range contains no numbers. Once at least one numeric reading exists, it computes the average. It assumes the input cells contain no spreadsheet errors: if they contain only errors and blanks, COUNT is also zero and this message would conceal those errors. Inspect error cells separately before using this guard.

=IF(COUNT(B2:B5)=0,"No readings",AVERAGE(B2:B5))

Try the data-quality checks

Start with the initial 10-minute result. Fill B4 with 0 and calculate again to get 7.5. Replace it with 6 to get 9: the four readings now sum to 36. Reset returns to the original missing observation.

To try the guarded formula, paste it into the formula field and clear B2:B5. The result should be No readings. Enter only 0 in B3: the result should now be 0, because one real numeric observation exists.

Further reading

• Microsoft: AVERAGE function
• Microsoft: COUNT function