Home › Excel functions › AVERAGE
Average Formula in Excel: AVERAGE, AVERAGEIF and More
AVERAGE returns the arithmetic mean of a set of numbers: their sum divided by how many there are. The tricky part is knowing which cells it counts, because blanks, text and zeros are treated differently.
AVERAGE · In this guide
AVERAGE syntax
=AVERAGE(number1, [number2], …)Like SUM, it takes numbers, cells or ranges, up to 255 arguments.
Basic example
| A | B | |
|---|---|---|
| 1 | Student | Score |
| 2 | Anna | 72 |
| 3 | Ben | 55 |
| 4 | Chris | 90 |
| 5 | Dana | 0 |
| 6 | Eli | |
| 7 | Average | 54.25 |
The result is 54.25: (72 + 55 + 90 + 0) ÷ 4. Notice that Eli's empty cell is not counted, but Dana's 0 is.
What AVERAGE counts and skips
| Cell contains | In a range like B2:B6 |
|---|---|
| A number, including 0 | Counted |
| An empty cell | Skipped |
| Text, including numbers stored as text | Skipped |
| TRUE / FALSE | Skipped |
| An error such as #N/A | The whole result becomes that error |
So a blank and a zero are not the same thing. If you type 0 for "no score yet", it pulls the average down.
Average without the zeros
Use AVERAGEIF with the condition "not equal to zero":
=AVERAGEIF(B2:B6, "<>0")Now Dana's 0 is left out and the result is 72.33: (72 + 55 + 90) ÷ 3.
Average with conditions: AVERAGEIF and AVERAGEIFS
=AVERAGEIF(range, criteria, [average_range])The average sale in the North region, with regions in column A and sales in column C:
=AVERAGEIF(A2:A6, "North", C2:C6)For several conditions, use AVERAGEIFS. Like SUMIFS, the range to average comes first:
=AVERAGEIFS(C2:C6, A2:A6, "South", C2:C6, ">500")The criteria work the same way as in SUMIF and COUNTIF: ">100", wildcards like "A*", or a cell reference.
Median, mode and other "averages"
- MEDIAN returns the middle value. It's less affected by a few extreme values than AVERAGE:
=MEDIAN(B2:B6). - MODE.SNGL returns the value that occurs most often.
- AVERAGEA also counts text (as 0) and TRUE/FALSE. You'll rarely need it.
- For an average of only the visible rows in a filtered list, use
=SUBTOTAL(101, B2:B6). See SUBTOTAL.
Common errors
- #DIV/0!: there are no numbers in the range (or none that meet the AVERAGEIF condition). Wrap it to show something friendlier:
=IFERROR(AVERAGE(B2:B6), ""). See IFERROR. - The average is too high or too low: zeros are being counted that should be blanks, or numbers are stored as text and skipped.
- Too many decimals: format the cell, or round the result with ROUND:
=ROUND(AVERAGE(B2:B6), 1).
Frequently asked questions
Does AVERAGE ignore blank cells?
Yes. Empty cells in a range are skipped. Cells containing 0 are included.
How do I average only cells that aren't zero?
Use =AVERAGEIF(range, "<>0").
Why does AVERAGE return #DIV/0!?
The range contains no numbers, so Excel would have to divide by zero. Check that the cells contain real numbers and not text.
What's the difference between AVERAGE and MEDIAN?
AVERAGE adds everything and divides by the count. MEDIAN returns the middle value when the numbers are sorted, so a single very large or small value affects it much less.