The AVERAGE function in Excel
AVERAGE adds the numbers in a range and divides by how many numbers there are: the average score, the average monthly cost, the average order value.
Text and empty cells are left out of both the total and the count. A cell containing 0, however, counts as a number and pulls the average down.
Syntax
=AVERAGE(number1, [number2], …)
| Argument | What it means |
|---|---|
| number1 | The first number, cell or range, e.g. B2:B6. |
| number2, …optional | More numbers, cells or ranges. Up to 255. |
Examples
1. Average of a column
| A | B | |
|---|---|---|
| 1 | Month | Expenses |
| 2 | Jan | 820 |
| 3 | Feb | 760 |
| 4 | Mar | 905 |
| 5 | Apr | 690 |
| 6 | May | 845 |
| 7 | Average | 804 |
4,020 in total over 5 months = 804 per month.
2. Text and empty cells are skipped
| A | B | |
|---|---|---|
| 1 | Student | Score |
| 2 | Noa | 72 |
| 3 | Liam | absent |
| 4 | Maya | 91 |
| 5 | Dan | |
| 6 | Eli | 85 |
| 7 | Average | 82.6667 |
Only Noa, Maya and Eli have numbers: (72 + 91 + 85) / 3 ≈ 82.67. "absent" and Dan's empty cell don't count. Had they been 0, the average would drop to 49.6.
3. Round the average
| A | B | |
|---|---|---|
| 1 | Student | Score |
| 2 | Noa | 72 |
| 3 | Liam | absent |
| 4 | Maya | 91 |
| 5 | Dan | |
| 6 | Eli | 85 |
| 7 | Average | 82.7 |
Wrap it in ROUND to keep one decimal. This changes the value itself, not just how it looks.
Common mistakes
Zeros for missing data. Typing 0 for "no score yet" counts as a real result and lowers the average.
Fix: Leave the cell empty, or write a word like "absent".
#DIV/0! error. The range has no numbers at all, so there's nothing to divide by.
Fix: Check the range, or use
=IFERROR(AVERAGE(B2:B6),"")for lists that may be empty.Averaging averages. The average of two group averages is wrong when the groups have different sizes.
Fix: Average all the original values together instead.
Frequently asked questions
How do I average only some values?
AVERAGEIF averages the values that meet one condition, AVERAGEIFS several, e.g. the average order in the North region.
What's the difference between AVERAGE and MEDIAN?
AVERAGE is the mean. MEDIAN is the middle value, which isn't pulled up or down by a few extreme values.
Related functions
- SUMSUM adds up numbers, cells and whole ranges in one formula.
- MINMIN returns the smallest number in a range or list of values.
- MAXMAX returns the largest number in a range or list of values.
- COUNTCOUNT counts cells that contain numbers; COUNTA counts every non-empty cell; COUNTBLANK counts empty ones.