Everybody knows what an average is, which is why nobody checks the denominator. The AVERAGE function decides that for you, and it does not count the rows you thought it counted.
In fact, two cells that both look empty can behave completely differently here. One of them lowers the answer and the other does not go into the sum at all.
For instance, a survey with unanswered questions and a survey with zero scores produce different numbers from the same column. So the question is never just the total; it is what the total got divided by.
The AVERAGE function.
So point it at a range and read the answer back.
=AVERAGE(B2:B5)
Numbers go into the sum and into the count together. In short, the AVERAGE function adds what it finds and divides by how many things it found, which is the definition right up until a cell is empty.
Text and logical values are skipped entirely. Therefore a column mixing scores with the word n/a averages only the scores, quietly, and never mentions the rows it left out.
A blank and a zero are not the same.
This is the whole card. An empty cell leaves the denominator alone, while a zero joins the sum and pushes the answer down.
=SUM(B2:B5)/COUNTA(A2:A5)
Both readings are defensible and only one of them is what the function does. Four scores of eight, zero, nothing and four average to four, because three numbers were found rather than four rows.
Divide the same points by the row count instead and you get three. Consequently the gap between the two answers is entirely a question about what an unanswered row means, which a formula cannot settle for you.
Result of the AVERAGE function.
Before. Four reps, one of whom never returned a score.
After. Twelve points over three scores, and the same twelve over four rows.
B2 -> 8 B3 -> 0 B4 -> (genuinely empty) B5 -> 4 =AVERAGE(B2:B5) -> 4 =SUM(B2:B5)/COUNTA(A2:A5) -> 3 =COUNT(B2:B5) -> 3 =AVERAGEA(B2:B5) -> 4 (blanks skipped here too)
AVERAGEA does not settle it either, though people reach for it expecting that. It counts text and logical values as zero, yet it still skips a genuinely empty cell, so over this range it returns four as well.
Finally, the counting functions are where this really lives. COUNTA explains which cells register as present, and a number stored as text is invisible to both until VALUE has been through it.