What it does
AVERAGE takes one or more ranges, adds up the numbers in them and divides by the count. =AVERAGE(B2:B13) returns the average of twelve months. The result equals SUM divided by COUNT.
Empty cells and text are left out, but 0 is included. The difference matters: type 0 for a month with no sales and the average drops; leave it empty and that month is treated as if it did not exist. Which one is right depends on your question, so fill the list accordingly.
For averages with conditions use AVERAGEIF and AVERAGEIFS. If a few very large or very small values pull the average, MEDIAN, which returns the middle value, can be a better summary. For an average weighted by quantity you need SUMPRODUCT.
Syntax
=AVERAGE(number1, [number2], ...)=ORTALAMA(sayı1; [sayı2]; ...)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
number1 | Required | The first number, cell or range to average. |
number2 | Optional | Optional further numbers, cells or ranges, up to 255 arguments. |
Examples
1Each rep's three-month average
The formula in E2 is filled down and averages the three months on each row.
=AVERAGE(B2:D2)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Jan | Feb | Mar | Average |
| 2 | Emma | 48,500 | 52,300 | 50,100 | 50,300.0 |
| 3 | Liam | 31,200 | 29,750 | 35,400 | 32,116.7 |
| 4 | Olivia | 61,000 | 58,250 | 64,700 | 61,316.7 |
| 5 | Noah | 22,800 | 27,100 | 24,950 | 24,950.0 |
- E2 (filled down to E5)
=AVERAGE(B2:D2)
Emma's average is 50,300.0 and Olivia's is 61,316.7.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Empty cell versus zero
Liam made no sales in March and the cell holds 0. Olivia's March figure has not arrived yet, so her cell is empty. Both rows have the same January and February figures.
=AVERAGE(B2:D2)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Jan | Feb | Mar | Average |
| 2 | Liam | 30,000 | 36,000 | 0 | 22,000.0 |
| 3 | Olivia | 30,000 | 36,000 | 33,000.0 |
- E2 (filled down to E3)
=AVERAGE(B2:D2)
Liam's average is divided by three months: 22,000.0. Olivia's is divided by two: 33,000.0. An empty cell is left out; a 0 is counted.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Flag values above the average
Column C compares each store's sales with the average of all stores. The average range is locked with $, so it does not shift when the formula is filled down.
=IF(B2>=AVERAGE($B$2:$B$6),"Above average","Below average")| A | B | C | |
|---|---|---|---|
| 1 | Store | Sales | Status |
| 2 | Camden | 184,000 | Above average |
| 3 | Leeds | 126,500 | Below average |
| 4 | York | 141,200 | Below average |
| 5 | Bristol | 203,750 | Above average |
| 6 | Hull | 98,400 | Below average |
- C2 (filled down to C6)
=IF(B2>=AVERAGE($B$2:$B$6),"Above average","Below average")
Camden and Bristol show Above average; the other three stores show Below average.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #DIV/0!
Cause: There are no numbers in the range: the cells are empty or the numbers are stored as text.
Fix: Check the range and how the numbers are stored; to show text instead of an error for an empty range, use
=IFERROR(AVERAGE(B2:B13),"No data").- Average too low
Cause: Periods without data were filled with 0, and AVERAGE counts those zeros.
Fix: Leave cells without data empty, or use
=AVERAGEIF(B2:B13,"<>0").- Average of too few values
Cause: Numbers stored as text are not included, so the average is based on fewer values.
Fix: Convert those cells with VALUE or with Convert to Number from the warning icon.
Good to know
- To see an average without a formula, select the cells; the status bar shows it.
- If later calculations should use the rounded average, use
=ROUND(AVERAGE(B2:B13),2); for appearance only, cell formatting is enough.
In Nexell
Get Nexell to do it from a sentence
Add an Average column with each rep's average sales over the three months
Convert to tableBring your sales table into the tool and type this. Nexell adds the Average column with real Excel formulas and shows it in the preview first; in the downloaded file, the average follows any change in a month's sales.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I average in Excel excluding zeros?
=AVERAGEIF(B2:B13,"<>0") averages the values that are not zero. Empty cells are already left out of an average.
What is the difference between average and median?
The average divides the total by the count, so a single very large value pulls it up. MEDIAN returns the middle value of the sorted list and is not affected by extremes.