AVERAGE - Find the mean of your data
Calculate a mean score, weekly sales, or resolution time. Understand which cells count and why a zero is different from a missing value.
By the end, you can
- Calculate an arithmetic mean using AVERAGE.
- Understand how blanks, text, and zero affect the result.
- Choose between a total, a count, and a mean.
Before you start: Know how to read a cell range such as B2:B5. Cell References and SUM provide useful foundations.
In this lesson
What AVERAGE does
AVERAGE adds the numeric values and divides by how many numeric values there are. This is the arithmetic mean. Use it to compare a team's mean assessment score, a store's average weekly sales, or the mean time taken to resolve tickets.
=AVERAGE(2,4,3,7)The total is 16 across four values. Divide 16 by 4 to get a mean of 4.
Syntax and arguments
=AVERAGE(number1, [number2], ...)number1 is required; later arguments are optional. The square brackets and dots describe the syntax and should not be typed into the actual formula.
| Argument | Purpose | Example |
|---|---|---|
number1Required | The first number, cell reference, or range to include in the mean. | B2:B5 |
number2, …Optional | Additional numbers, cells, or ranges. Excel accepts up to 255 arguments in total; a range counts as one argument. | D2:D5 |
Step by step: employee assessment scores
An HR coordinator summarises four employee training assessments, each marked out of 100. All scores use the same scale, so each numeric score has equal weight.
85| A | B | |
|---|---|---|
| 1 | Employee | Score / 100 |
| 2 | Anika | 78 |
| 3 | Dev | 86 |
| 4 | Meera | 92 |
| 5 | Rahul | 84 |
| 6 | Mean score | 85 |
- Select B6, below the score column.
- Type =AVERAGE(B2:B5) and press Enter.
- Check the calculation: 78 + 86 + 92 + 84 = 340; 340 ÷ 4 = 85.
- If you copy this formula elsewhere, relative references move. Use $B$2:$B$5 when this exact group must stay fixed.
A sales example: keep zero-sales weeks
A Pune branch reports revenue for four weeks. Week 3 had no sales, recorded as a numeric zero. That is an observed result, not a missing report, so it belongs in the four-week average.
₹40,000| A | B | |
|---|---|---|
| 1 | Week | Sales |
| 2 | Week 1 | ₹48,000 |
| 3 | Week 2 | ₹52,000 |
| 4 | Week 3 | ₹0 |
| 5 | Week 4 | ₹60,000 |
| 6 | Weekly mean | ₹40,000 |
=AVERAGE(B2:B5)Total revenue is ₹1,60,000. There are four numeric weeks, including zero: 1,60,000 ÷ 4 = 40,000.
Different ways to use AVERAGE
=AVERAGE(B2:B5)In the employee example, use all four scores.
=AVERAGE(B2,B4)Use only Anika's 78 and Meera's 92. A comma separates cells; a colon would include the rows between them.
=AVERAGE(B2:B3,B4:B5)All four employee scores count once. Avoid overlapping ranges: a repeated cell contributes again.
=AVERAGE(70,80,90)References are easier to update in a report; direct numbers are useful for a quick calculation.
Blanks, text, zeros, and errors
- Blank cells are ignored. A range with 80, a blank, and 100 has two numeric values, so its mean is 90.
- Text, including a referenced value such as Pending or a number stored as text, is ignored. A formula returning an empty string is text and is also ignored.
- Zero is a number and is included. The mean of 80, 0, and 100 is 60.
- Logical values (TRUE and FALSE) in referenced cells are ignored.
- A referenced error, such as #N/A or #DIV/0!, causes the average to return an error. Fix the source problem rather than silently excluding it.
- With no numeric values to average, Excel returns #DIV/0!. This differs from SUM, which returns 0 for a range with no numeric values.
Excel treats arguments typed directly differently from cells: =AVERAGE(10,"20",TRUE) returns 31/3 (about 10.33), because the numeric text and TRUE are converted. Prefer numeric cells for predictable reports. AVERAGE also includes hidden and filtered-out rows; a visible-row mean requires another approach such as SUBTOTAL.
AVERAGE vs SUM and COUNT
- SUM(B2:B5) returns the combined score: 340.
- COUNT(B2:B5) returns the number of numeric scores: 4.
- AVERAGE(B2:B5) returns the mean score: 85.
=SUM(B2:B5)/COUNT(B2:B5)For this error-free numeric range, this gives the same mean as AVERAGE. AVERAGE expresses the intention more directly; neither formula can produce a mean when there are no numeric inputs.
Common mistakes and practical tips
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.