Function guides · Statistical
AVERAGE function
Returns the arithmetic mean of numbers.
Syntax
=AVERAGE(number1, [number2], ...)
| Argument | What it is |
|---|---|
number1 | A number, cell reference, or range. |
number2, ... | Optional. Up to 255 more. |
What AVERAGE does
AVERAGE adds up the numbers it is given and divides by how many numbers there were. That is the arithmetic mean, the figure most people mean when they say “average”.
What counts as a number matters more than the arithmetic. Blank cells, text and logical values inside a range are skipped. A cell holding 0 is a real number and is included. A value typed straight into the formula, such as =AVERAGE(10, TRUE), is treated differently again, and TRUE counts as 1. Nobody should rely on that. For a mean that depends on a condition, use AVERAGEIF or AVERAGEIFS.
AVERAGE examples
1. Average test scores
| A | B | |
|---|---|---|
| 1 | Student | Score |
| 2 | Ana | 92 |
| 3 | Ben | 78 |
| 4 | Chloe | 85 |
| 5 | Dev | 64 |
| 6 | Eli | 86 |
| 7 | 81 |
Five scores add up to 405, and AVERAGE divides by 5. The answer, 81, is the mean, not the middle value. If you need the middle value, use MEDIAN.
2. A blank cell is skipped, a zero is not
| A | B | |
|---|---|---|
| 1 | Student | Score |
| 2 | Ana | 92 |
| 3 | Ben | 78 |
| 4 | Chloe | 85 |
| 5 | Dev | |
| 6 | Eli | 86 |
| 7 | 85.25 |
Dev missed the exam. With the cell left blank, AVERAGE ignores it and divides by four. If someone had typed 0, the divisor would be five and the mean would drop to 63.
Decide which one is true before you build the sheet. A zero says "scored nothing". A blank says "no data".
3. Average with a cell reference as the cutoff
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Units | Cutoff | 300 |
| 2 | Dana | 410 | ||
| 3 | Luis | 295 | ||
| 4 | Mei | 520 | ||
| 5 | Omar | 180 | ||
| 6 | ||||
| 7 | 465 |
To average only some of the rows, reach for AVERAGEIF. This one averages the sales of reps with at least 300 units, using a threshold typed in D1 so it can be changed without editing the formula.
Dana (410) and Mei (520) clear the bar. Luis (295) and Omar (180) do not, so the answer is the mean of 410 and 520.
Common AVERAGE mistakes
- Treating blanks and zeros as the same. AVERAGE skips empty cells but counts zeros. A column where missing data was typed as 0 gives a mean that is too low. Clear the zeros, or switch to AVERAGEIF with the criteria ">0".
- Averaging averages. The mean of three department averages is not the company average unless every department has the same headcount. Add the underlying values and divide by the count, or use SUMPRODUCT to weight them.
- Numbers stored as text. Text values are ignored, so the average is computed on fewer numbers than you think. Compare COUNT(range) with the number of rows. If they differ, some cells are text.
- #DIV/0! It appears when there are no numbers at all in the range. Wrap the formula in IFERROR only when an empty range is a normal state, such as a month with no sales yet.
- Picking the wrong range after sorting or inserting. Hard-typed ranges do not grow when you add a row below the last one. Convert the data to a Table (Ctrl+T) or include a spare blank row in the range.
AVERAGE on Excel job tests
The plain version asks for the mean of a column. The disguised versions are more common: "average order value for the West region" is AVERAGEIF, and "average excluding the highest score" is the total minus MAX, divided by the count minus one.
Check the range covers the last row of data. If the task says to round to one decimal, wrap the whole formula in ROUND rather than only changing the number format, so the displayed value and the stored value agree.
Read what the task says about missing scores. If it says count them as zero, enter zeros. If it says nothing, leave blanks blank, because AVERAGE skips them and a typed zero would pull the mean down.
Related functions
AVERAGE questions
- Does AVERAGE ignore zeros?
- No. It ignores blank cells and text, but a cell containing 0 is a number and counts toward both the total and the divisor. Use AVERAGEIF(range, ">0") to leave zeros out.
- How do I get a weighted average?
- Divide SUMPRODUCT of the values and weights by SUM of the weights:
=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5). AVERAGE cannot weight. - What is the difference between AVERAGE and MEDIAN?
- AVERAGE adds the numbers and divides by how many there are. MEDIAN returns the middle value after sorting. One huge outlier moves the average a lot and the median barely at all.
- Can AVERAGE handle a whole column?
- Yes,
=AVERAGE(B:B)works and ignores the header text. Keep the formula outside column B.
Practice AVERAGE
Try it in a working grid: the free lesson Averages (module 4 of Excel from Zero) has checked AVERAGE exercises.
Practice this function