AVERAGE Function in Excel: Examples and Common Traps
How to use AVERAGE in Excel to find the typical value, why blanks and zeros change the answer, and when MEDIAN, AVERAGEIF or a weighted average is better.
By Ayoub Farid · · 2 min read
AVERAGE gives you the typical value of a list: average monthly sales, average score, average order. It's simple to write, but blanks, zeros and outliers can quietly change what the number means.
The syntax
=AVERAGE(number1, [number2], ...)It adds the numbers and divides by how many there are.
Example
| A | B | |
|---|---|---|
| 1 | Month | Sales |
| 2 | Oct | 5,300 |
| 3 | Nov | 6,100 |
| 4 | Dec | 7,400 |
=AVERAGE(B2:B4)Result: 6,266.67.
Blanks vs zeros: the big trap
AVERAGE ignores empty cells but counts zeros.
- Data 5,300 / blank / 7,400 → average 6,350 (two values)
- Data 5,300 / 0 / 7,400 → average 4,233.33 (three values)
So if "no sales yet" is typed as 0, your average drops. Decide which you mean. To ignore zeros:
=AVERAGEIF(B2:B13,"<>0")Text in the range
AVERAGE ignores text cells too. If a cell says "n/a", it's left out. That's usually fine, but it means the count of values may not be what you think. Check with COUNT.
When AVERAGE misleads
One huge month drags the average up. For salaries, house prices or delivery times, the median often describes the "typical" case better:
=MEDIAN(B2:B13)Average with a condition
Average only Food items:
=AVERAGEIF(D2:D20,"Food",B2:B20)More in AVERAGEIF.
Weighted average
If items have different importance (credits per course, units sold per price), a plain AVERAGE is wrong. Use SUMPRODUCT:
=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)Full guide: weighted average.
Rounding the result
=ROUND(AVERAGE(B2:B13),0) rounds to a whole number. Formatting only changes how it looks; ROUND changes the value.
Practise AVERAGE
Cellio's AVERAGE lesson ("AVERAGE finds the middle ground") asks real questions like "What are average monthly sales? Write it in B15", then checks your formula on hidden versions of the data, so =SUM(B2:B13)/12 also counts as correct if it really works.
Frequently asked questions
How do I calculate an average in Excel?
Write =AVERAGE( and select the range, e.g. =AVERAGE(B2:B13).
Does AVERAGE include blank cells?
No. Blank cells are ignored, but cells containing 0 are included.
How do I average without zeros?
Use =AVERAGEIF(B2:B13,"<>0").
What is the difference between AVERAGE and MEDIAN?
AVERAGE is the mean of all values; MEDIAN is the middle value and isn't pulled by extreme outliers.