The Excel AVERAGE function, explained interactively.
AVERAGE returns the arithmetic mean of a list of numbers. Like MIN and MAX it silently skips empty cells, text, and booleans — useful most of the time, quietly wrong when you expected those to count. The twist: AVERAGE does include zeros, which trips people who assumed a blank and a zero are the same.
How to use AVERAGE
- Type
=AVERAGE(and select the range of numbers — one contiguous range or a list of ranges and literal numbers. - Close the parenthesis. AVERAGE returns the sum of numeric values divided by their count. Text, blanks, and booleans are silently ignored.
- Zeros are included. For a non-zero mean, use
AVERAGEIF(range, "<>0"). - For a conditional mean (“average score for Active students”), use
AVERAGEIF/AVERAGEIFSinstead.
The demo has one text cell ("Absent") and one blank. AVERAGE skips both — only the 6 numeric scores contribute to the mean.
| A | B | D | |
|---|---|---|---|
| 1 | Student | Score | Average |
| 2 | Ava | 92 | 70.67 |
| 3 | Ben | 58 | |
| 4 | Chloe | 75 | |
| 5 | Dev | 47 | |
| 6 | Elena | Absent | |
| 7 | Farid | 64 | |
| 8 | Grace | (blank) | |
| 9 | Harvey | 88 |
=AVERAGE(B2:B9). AVERAGE skipped the text cell ("Absent") and the blank cell — it averages 6 numeric values and returns their mean, 70.67.AVERAGE syntax and arguments
One required argument, up to 255 total. See Microsoft’s official AVERAGE reference for the canonical specification.
AVERAGE(TRUE, 5)) are coerced to 1/0. Zero values are counted — they lower the mean like any other number.AVERAGE examples
Four patterns that cover nearly every real-world AVERAGE use.
Example 1: AVERAGE for a plain mean
The canonical case — mean of a column of scores, prices, or times.
Returns the arithmetic mean of every numeric value in B2:B100. Text and blanks are silently skipped; zeros are counted. Returns #DIV/0! if the range has no numbers at all.
Example 2: AVERAGE excluding zeros
Swap to AVERAGEIF when blanks and zeros should be treated the same way.
Includes only non-zero values in the mean. Zeros are excluded from both the sum and the count. Common for revenue or engagement metrics where zero means “no data” rather than “zero activity”.
Example 3: AVERAGE across multiple ranges
AVERAGE accepts up to 255 arguments — combine disjoint ranges without a helper column.
Returns the mean across all three ranges combined — as if they were one long list. Each range is weighted by its actual count of numeric values, not by its declared size.
Example 4: AVERAGE wrapped in IFERROR for dashboards
Guard against #DIV/0! when the range might be empty or filtered down to nothing.
FILTER narrows the range to rows where A equals the user’s input in E1; AVERAGE takes the mean; IFERROR shows "—" instead of #DIV/0! when nothing matches.
Common AVERAGE errors and fixes
Four failure modes, each with what to check and how to recover.
AVERAGE returns #DIV/0!
Cause: no numeric values in the range — either everything is text/blank, or numbers are stored as text and AVERAGE can’t see them.
Check with COUNT(range). If it returns 0, the range truly has no numbers; if it returns fewer than you expect, values are text-coerced. Fix with Data → Text to Columns → Finish, or wrap in IFERROR for a cleaner display.
AVERAGE dragged down by zeros
Cause: zeros are counted. A column of 50 real values + 20 zero-filled empty rows averages much lower than the real-value mean.
Switch to AVERAGEIF(range, "<>0"), or filter zeros out of the source before averaging. The right choice depends on whether zero means “no activity” (exclude) or “real zero” (include).
AVERAGE skewed by outliers
Cause: one or two extreme values drag the mean away from where most data sits — classic for salary, response time, order size.
Switch to MEDIAN, or use TRIMMEAN(range, 0.2) to drop the top and bottom 10% before averaging. Both are more robust to outliers than AVERAGE.
AVERAGE ignoring cells that look numeric
Cause: numbers stored as text — the classic CSV-import artefact. AVERAGE silently skips them like any other text.
Run =ISNUMBER(B2) on a suspect cell. Coerce with Data → Text to Columns → Finish, or multiply by 1 in a helper column to force numeric conversion.
AVERAGE vs AVERAGEIF, MEDIAN & AVERAGEA
Four measures of centre. Pick based on whether you want a plain mean, a filtered mean, the middle value, or text-as-zero behaviour.
| Function | Returns | Text / blank handling | Use when |
|---|---|---|---|
| AVERAGE | Arithmetic mean | Skipped silently | Clean numeric data with no outliers |
| AVERAGEIF | Mean of matching rows | Skipped silently | “Mean for Active users” — one condition |
| MEDIAN | Middle value | Skipped silently | Data with outliers — salary, latency, price |
| AVERAGEA | Mean with text/booleans as 0/1 | Coerced to 0/1 — included | Rare. Flag-coded survey data only. |
Rule of thumb: start with AVERAGE; reach for AVERAGEIFS when you need conditions; switch to MEDIAN when the distribution is skewed. AVERAGEA is a specialty tool — you’ll know when you need it.
AVERAGE frequently asked questions
6.01Does AVERAGE include zeros?▸
Yes. AVERAGE treats 0 as a real data point and includes it in the mean. To exclude zeros, use AVERAGEIF: =AVERAGEIF(B2:B100, "<>0"). This is the single most common surprise — people who want a “non-zero average” need AVERAGEIF, not AVERAGE.
6.02Does AVERAGE count text or blank cells?▸
No. AVERAGE silently skips any non-numeric value — text like "Absent", empty cells, and booleans are all ignored. They don’t count toward the denominator. If you want text coerced (TRUE = 1, FALSE = 0, text = 0), use AVERAGEA.
6.03Why does AVERAGE return #DIV/0!?▸
Because every cell in the range is non-numeric — text, blanks, or booleans — leaving nothing to divide by. Wrap in IFERROR to substitute a fallback: =IFERROR(AVERAGE(range), "—"), or use =IF(COUNT(range)=0, "—", AVERAGE(range)) to surface the empty case explicitly.
6.04How do I average only values that meet a condition?▸
Use AVERAGEIF for one condition: =AVERAGEIF(A2:A100, "Food", C2:C100). Use AVERAGEIFS for multiple conditions AND’d together. Both skip non-numeric cells the same way AVERAGE does.
6.05When should I use MEDIAN instead of AVERAGE?▸
Use MEDIAN when the distribution has outliers — one extreme value can drag AVERAGE far from where the data actually sits. Salary, response-time, and listing-price data are classic cases. AVERAGE is fine for normally-distributed data where each point pulls its weight.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.