AVERAGE returns the arithmetic mean of the numbers you give it, adding all numeric values and dividing by how many there are. It ignores blank cells and text, making it a fast way to find the typical value in a range.
=AVERAGE(number1, [number2], ...)What does AVERAGE do?
The AVERAGE function calculates the arithmetic mean of a set of numbers. You give it numbers, cell references, or ranges, and it adds them up and divides by the count of numeric values. It is one of Excel's most-used statistical functions because it turns a column of figures into a single, meaningful summary.
Averages matter because they smooth out noise and reveal the typical value in data. Whether you are checking average sales per region, average test scores, or average monthly expenses, AVERAGE saves you from manual addition and division. It also updates automatically when your data changes, so your summary stays current.
Imagine you manage a small team and need to report the average order value for last month. Instead of adding each order and dividing by the count, you type =AVERAGE(B2:B10) and get the answer instantly. This guide walks through that and more realistic uses, from simple ranges to conditional averages.
When to use AVERAGE
- Finding the average sales figure across regions or products.
- Calculating average test scores or grades for a class.
- Summarising average monthly expenses or budgets.
- Reporting average order value or customer spend.
- Checking average delivery times or response times.
- Comparing average performance between teams or periods.
AVERAGE syntax
=AVERAGE(number1, [number2], ...)| Argument | Required? | What to enter |
|---|---|---|
number1 | Required | The first number, cell reference, or range you want to average. Example: B2:B10 or 1200. |
number2 | Optional | Additional numbers, references, or ranges to include. You can add up to 255 arguments. Example: C2:C10 or 500. |
What it returns: A single number: the arithmetic mean of all numeric values supplied. Text, blanks, and logical values in ranges are ignored; logical values and text representations of numbers typed directly into the formula are counted.
AVERAGE examples
1 Average a simple range of sales figures
The goal is to find the average sales amount for a short list of daily sales. The formula =AVERAGE(B2:B6) adds the five numbers (1200 + 1500 + 900 + 1800 + 1100 = 6500) and divides by 5. It returns 1300, the typical daily sales figure. This is the most common use of AVERAGE.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Day | Sales | |||
| 2 | Mon | 1200 | 1300 | ||
| 3 | Tue | 1500 | |||
| 4 | Wed | 900 | |||
| 5 | Thu | 1800 | |||
| 6 | Fri | 1100 | |||
| 7 |
2 Average with multiple ranges and a constant
Here you want the average of two separate score columns plus a bonus constant. The formula =AVERAGE(B2:B4, C2:C4, 10) includes six cell values and the number 10. It adds 80 + 90 + 70 + 85 + 95 + 75 + 10 = 505 and divides by 7. The result is approximately 72.14, showing how AVERAGE handles mixed arguments.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Score 1 | Score 2 | |||
| 2 | Ann | 80 | 85 | 72.14285714 | ||
| 3 | Ben | 90 | 95 | |||
| 4 | Cara | 70 | 75 | |||
| 5 |
3 Average monthly expenses excluding blanks
You have monthly expenses but some months are blank because no cost was recorded. The formula =AVERAGE(B2:B7) ignores the blank cells and averages only the four numeric values: 400 + 450 + 500 + 550 = 1900 divided by 4. It returns 475, the average of months with actual spending. This shows that AVERAGE does not treat blanks as zero.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Expense | |||
| 2 | Jan | 400 | 475 | ||
| 3 | Feb | 450 | |||
| 4 | Mar | ||||
| 5 | Apr | 500 | |||
| 6 | May | ||||
| 7 | Jun | 550 | |||
| 8 |
Common AVERAGE errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #DIV/0! | The range you are averaging contains no numeric values, so Excel cannot divide by zero. | Check that the range includes numbers and not just text or blanks. Use =IFERROR(AVERAGE(B2:B10), 0) to display 0 instead of the error. |
| #VALUE! | One of the arguments is text that cannot be interpreted as a number, and it was typed directly into the formula rather than referenced from a cell. | Remove or correct the text argument. If you need to ignore text in a range, AVERAGE already ignores it; only direct text arguments cause this error. |
| #NAME? | The function name is misspelled, such as =AVERGE(B2:B10). | Type =AVERAGE( and let Excel suggest the function, or correct the spelling. |
| Wrong result (too low) | Blank cells or cells containing zero are being treated as zero, or you included a range with zeros that should be excluded. | Use =AVERAGEIF(B2:B10, "0") to average only non-zero values, or ensure blanks are truly empty rather than zero. |
Tips for using AVERAGE
- Use a whole-column reference like
B:Bonly when the column contains just your data; otherwise limit the range to avoid including headers or totals. - Remember that AVERAGE ignores text and blanks in ranges, but counts zeros. If zeros should be excluded, use AVERAGEIF.
- Press
Alt+=after selecting an empty cell below a column of numbers to insert an AVERAGE formula automatically. - Combine AVERAGE with ROUND, for example
=ROUND(AVERAGE(B2:B10), 2), to control decimal places in reports. - Use AVERAGEIF or AVERAGEIFS when you need the average for a subset, such as one region or one month.
- Check for hidden rows or filtered data: AVERAGE includes hidden rows, while SUBTOTAL with function number 101 averages only visible rows.
Frequently asked questions
What is the difference between AVERAGE and AVERAGEA?
AVERAGE ignores text and logical values in ranges, while AVERAGEA counts text as 0 and logical values as 1 or 0. Use AVERAGE for typical numeric data.
Does AVERAGE include zero values?
Yes, zeros in cells are included in the calculation. If you want to exclude zeros, use =AVERAGEIF(range, "0").
How do I average only visible cells after filtering?
Use =SUBTOTAL(101, range) or =AGGREGATE(1, 5, range). AVERAGE itself includes hidden and filtered-out rows.
Can AVERAGE handle dates?
Yes, dates are stored as numbers in Excel, so AVERAGE returns the average date as a serial number. Format the result cell as a date to display it correctly.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. AVERAGE reference