The guide · AVERAGE
What AVERAGE does
AVERAGE adds up the numbers you give it and divides by how many there are. =AVERAGE(B2:B6) on five test scores gives the class average, and it keeps working if you add a sixth score inside the range.
Syntax
=AVERAGE(number1, [number2], …)Why not just SUM and divide?
=SUM(B2:B6)/5 gives the same answer today, but the 5 is typed in by hand. Add a student and the formula is quietly wrong. AVERAGE counts the values for you, so the division always matches the data.
Blanks vs zeros
AVERAGE skips empty cells and text, but it does count zeros. If a student hasn't taken the test yet, leave the cell blank, not 0, or they will drag the average down. If you need to average only some rows, look at AVERAGEIF.
Common mistakes
- #DIV/0! means AVERAGE found no numbers at all, often because the range points at the wrong column.
- Including the header. Text headers are ignored, so this is harmless, but including the total row is not: it gets averaged in too.
- Too many decimals. Wrap it as
=ROUND(AVERAGE(B2:B6), 1)when you need a tidy number.
FAQ
- How do I calculate an average in Excel?
- Use =AVERAGE( followed by the range, for example =AVERAGE(B2:B6). Excel adds the numbers and divides by how many there are.
- Does Excel AVERAGE include blank cells?
- No. Blank cells and text are skipped. Cells containing 0 are included and lower the average.