AVERAGE: understand what is included
AVERAGE is easy to read, but a missing entry, a text value and a recorded zero mean different things. Look at the inputs before judging the result.
Formula syntax
=AVERAGE(number1, [number2], ...)
What goes into it
number1
First value or range.
number2
Optional additional values/ranges.
A quick example first
=AVERAGE(10,20,30)
Returns 20.
=AVERAGE(B2:B20)
Returns the arithmetic mean of numeric values in B2:B20.
Decide whether a missing observation should count as zero
Enter the values below in B2:B5 and leave B4 empty.
| Cell | Value |
|---|---|
| B2 | 10 |
| B3 | 0 |
| B4 | (empty) |
| B5 | 20 |
| Formula | Expected result / interpretation |
|---|---|
| =AVERAGE(B2:B5) | 10 — (10 + 0 + 20) / 3 |
| =COUNT(B2:B5) | 3 numeric observations |
| =SUM(B2:B5)/ROWS(B2:B5) | 7.5 — treats all four row slots as observations |
AVERAGE includes numeric zero and ignores the empty referenced cell. Those are different observations: a zero sale is a known result, while a blank may be unreported data. Using four as the denominator creates a lower average and implicitly gives the missing row a zero contribution.
Before you copy this into a real sheet
Choose the denominator from the meaning of the data. Do not replace missing readings with zero simply to remove blanks. Referenced text values are ignored, which can also hide imported numeric text; compare COUNT with your expected observation count. For weighted results such as average selling price, calculate total revenue divided by total units or use a suitable weighted formula. Averaging row-level prices equally is wrong when quantities differ.
Useful habits
- Blank cells in a referenced range are ignored, while zero values are included.
- Decide whether missing values and zeros mean different things in the dataset.
- Use AVERAGEIF/AVERAGEIFS for conditional averages.
Where people get caught out
- Treating blanks as zeros when interpreting the result.
- Averaging averages from groups of different sizes instead of averaging the underlying observations.
Other functions worth a look
A detail worth remembering
If zero means “no observation” in your workflow, repair the source data policy rather than hoping AVERAGE will guess what it represents.
Reference and examples
The examples above use small, invented datasets and English-language formula names. Check your Excel version and argument separator against the official Excel function reference if a formula behaves differently on your computer.