AVERAGE — The Arithmetic Mean
Excel's most-used statistical function. Sum of numbers ÷ count of numbers. Everyone knows it — few know the traps. This page covers all of them: the zero-vs-blank trap, the AVERAGEA text quirk, weighted averages with SUMPRODUCT, and when to reach for MEDIAN instead.
_xlfn. prefix. The most portable function in Excel — ship it anywhere without worry.The fix: if "no data" should be excluded, leave those cells actually blank — don't fill them with 0. If you must use 0 as a placeholder, use AVERAGEIF with a ">0" criterion, or an IF-based array pattern.
Syntax breakdown
AVERAGE is unusually flexible — you can pass in single numbers, cell references, ranges, or any combination. All get flattened into one list of numeric values before averaging.
| Argument | Type | What it does |
|---|---|---|
number1 |
REQUIRED | The first value or range to average. Can be a number literal (5), a cell reference (A2), a range (A2:A20), or a named range (SalesAmount). |
number2, ... |
OPTIONAL | Additional values or ranges. Up to 255 arguments in Excel 2007+ (30 in Excel 2003). Mix and match: =AVERAGE(A2:A10, B5, 100, C1:C5) is perfectly valid. |
Five working examples
Every example uses the 18-row Q1 2026 sales log from the workbook. Named ranges: SalesAmount, SalesRep, SalesProduct, SalesRegion.
01 Basic AVERAGE — the mean of a range
The everyday use: get the mean of a column, a slice of a column, or a mixed set of cells.
Partial range
Manually listed cells
Mixed literals and references
| Formula | What it does | Result |
|---|---|---|
| =AVERAGE(SalesAmount) | All 18 sales | $6,022.22 |
| =AVERAGE(E4:E8) | First 5 rows | $6,180.00 |
| =AVERAGE(E19:E21) | Last 3 rows | $5,166.67 |
| =AVERAGE(SalesAmount,B2) | 18 sales + one manual value in B2 | Depends on B2 |
02 ⚠️ AVERAGE vs AVERAGEA — the text trap
These two look identical but treat text and logical values differently. Pick wrong and your mean is silently corrupted.
Setup: a range with mixed content
Cells G16:G20 contain: 100, 200, 300, "text!", 500. Four numbers and one text cell. What should the mean be?
| Value | AVERAGE reads | AVERAGEA reads |
|---|---|---|
| 100 | 100 | 100 |
| 200 | 200 | 200 |
| 300 | 300 | 300 |
| "text!" | skipped | 0 (counted) |
| 500 | 500 | 500 |
| Result → | AVG=275, AVGA=220 | |
Also affects TRUE / FALSE
AVERAGE ignores logical values. AVERAGEA counts TRUE as 1 and FALSE as 0.
03 Weighted average — the SUMPRODUCT trick
AVERAGE treats every value with equal weight. Real analysis often needs weighted averaging — average deal size weighted by number of deals, GPA weighted by credit hours, portfolio return weighted by allocation.
The problem
Six reps have different average deal sizes AND different deal counts. A simple AVERAGE of the "average deal size" column overweights small-volume reps.
| Rep | Avg deal size | Deals closed |
|---|---|---|
| Emma | $5,925 | 4 |
| David | $5,800 | 4 |
| Michael | $8,300 | 3 |
| Sofia | $4,233 | 3 |
| Aisha | $6,150 | 2 |
| James | $5,800 | 2 |
Simple AVERAGE — every rep counts equally
Weighted AVERAGE — reps with more deals count more
Why they differ: Simple AVERAGE gives every rep 1 "vote" regardless of how many deals they closed. Weighted AVERAGE gives each rep a vote proportional to their deal count. The weighted result matches what you'd get by averaging ALL 18 individual sales — because that's what "weighted by deals" mathematically means.
The formula in words
SUMPRODUCT(values, weights) = 5925×4 + 5800×4 + 8300×3 + 4233×3 + 6150×2 + 5800×2 = 108,400
SUM(weights) = 4+4+3+3+2+2 = 18
Weighted avg = 108,400 ÷ 18 = $6,022.22
04 🎯 The outlier problem — when AVERAGE lies
One extreme value can shift AVERAGE dramatically. This is when to reach for MEDIAN instead.
Normal-looking data
Five values, cleanly clustered: 4000, 5000, 6000, 7000, 8000.
Add one outlier
Now add a $100,000 value to the same set:
💡 The takeaway
05 Conditional averaging — jump to AVERAGEIF
"Average sales by rep" or "average over $5,000" — these need a condition. AVERAGE alone can't filter; AVERAGEIF and AVERAGEIFS can.
Single condition — AVERAGEIF
Multiple conditions — AVERAGEIFS
Comparison: plain AVERAGE + manual filter (the hard way)
Interactive playground
Try it Live AVERAGE demonstration
Mirrors live cells from the workbook. Edit the yellow input → the blue answer updates.
Download the workbook to experiment with AVERAGE, AVERAGEA, MEDIAN, MIN, and MAX side-by-side.
The Basic Aggregates family — one range, four questions
Meet the family
Four sibling functions that take the same range and answer different questions about it. This page is AVERAGE — the mean.
All four are universal (Excel 1.0+) and treat text/logicals identically (ignored). All four have "A" variants (AVERAGEA, MINA, MAXA) that count text as 0 — usually undesirable. Reach for the plain versions by default.
Common errors and how to fix them
| Result | Why it happens | Broken → Fix |
|---|---|---|
| DIV/0 error | Range contains no numeric values (all blanks, all text). AVERAGE has nothing to divide by. | =AVERAGE(A2:A10) // if all cells contain text or are blank
=IFERROR(AVERAGE(A2:A10), 0) |
| Wrong low mean | Zero-values counted when you meant them to be "no data". Classic trap. | =AVERAGE(A2:A20) // if empty rows contain 0 instead of being blank
=AVERAGEIF(A2:A20, ">0") // skip zeros explicitly |
| VALUE error | A cell in the range contains an error like #N/A or #DIV/0! from an upstream formula. | =AVERAGE(A2:A20) // if any cell has an error
=AVERAGEIF(A2:A20, "<>#N/A") // or use IFERROR upstream |
| Off-by-N mean | AVERAGEA used accidentally, treating text cells as zero. | =AVERAGEA(A2:A20) // counts text as 0
=AVERAGE(A2:A20) // skips text properly |
| Wrong on averages | Averaging pre-averaged values with unequal weights. | =AVERAGE(B2:B7) // 6 rep averages, but rep sample sizes differ
=SUMPRODUCT(B2:B7, C2:C7) / SUM(C2:C7) // weighted |
| Skewed report | Data has outliers; AVERAGE misrepresents "typical" value. | Reporting AVERAGE on income/home-price data
Report MEDIAN — half above, half below |
Related functions
Complementary functions
Excel version compatibility
AVERAGE has been in Excel since version 1.0 (1985) — the ur-function. Every platform supports it identically.
| Platform | Supports AVERAGE? | Notes |
|---|---|---|
| Excel 365 (Windows & Mac) | ✓ Yes | Full support · 255 args |
| Excel 2021 / 2019 / 2016 / 2013 / 2010 / 2007 | ✓ Yes | Full support · 255 args |
| Excel 2003 | ✓ Yes | 30-arg limit |
| Excel for the web | ✓ Yes | Full support |
| Excel on iPad & iPhone | ✓ Yes | Full support |
| Google Sheets | ✓ Yes | Same syntax and behavior |
| LibreOffice Calc | ✓ Yes | Full support |
| Apple Numbers | ✓ Yes | Full support |
How to write an AVERAGE from scratch
-
Point at your range
The simplest AVERAGE takes one range:
=AVERAGE(A2:A100). Named ranges make it read cleaner:=AVERAGE(SalesAmount). -
Watch for zero-as-placeholder
If "no data" cells contain 0 instead of being blank, they drag your mean down. Leave those cells actually empty — or use
=AVERAGEIF(range, ">0")to skip zeros. -
For text/logicals, use plain AVERAGE (not AVERAGEA)
AVERAGE ignores text and TRUE/FALSE. AVERAGEA counts them as 0/1. You almost always want the plain version.
-
Need a condition? Use AVERAGEIF or AVERAGEIFS
Rather than writing
=AVERAGE(IF(...))as an array formula, use the purpose-built AVERAGEIF or AVERAGEIFS. -
Need weights? Use SUMPRODUCT ÷ SUM
When each value has a weight (deal count, credit hours, portfolio allocation), the correct formula is
=SUMPRODUCT(values, weights) / SUM(weights). -
Skewed data? Reach for MEDIAN
If your data has outliers or a long tail, AVERAGE misleads. Report MEDIAN instead — half above, half below, robust to extremes.
Functions used with AVERAGE
Frequently asked questions
What's the difference between AVERAGE and AVERAGEA?
AVERAGE ignores text and logical values (TRUE/FALSE). AVERAGEA counts text as 0 and TRUE as 1. You almost always want plain AVERAGE — AVERAGEA is for niche cases where non-numeric cells should count toward the mean as zero.
Does AVERAGE ignore blank cells?
Yes — empty cells are skipped, so a range with 20 rows but only 15 filled in gets averaged as 15 values. This is usually what you want. But if the "blank" cells actually contain zero, they WILL be counted — leading to a lower-than-expected mean.
Why is my AVERAGE lower than expected?
Most common cause: "no data" cells contain 0 instead of being genuinely blank. AVERAGE counts zeros. Fix: leave those cells empty, or use =AVERAGEIF(range, ">0") to skip zeros explicitly.
When should I use MEDIAN instead of AVERAGE?
When your data is skewed — income, home prices, response times, purchase amounts, anything with a long tail or occasional outliers. AVERAGE gets pulled toward extreme values; MEDIAN doesn't. Calculate both — if they differ significantly, MEDIAN is likely more honest. See the MEDIAN page.
How do I calculate a weighted average in Excel?
Use SUMPRODUCT ÷ SUM: =SUMPRODUCT(values, weights) / SUM(weights). Essential when averaging averages from different-sized groups, calculating GPA with credit hours, or weighting portfolio returns by allocation.
Can AVERAGE handle multiple ranges?
Yes — up to 255 arguments in Excel 2007+, 30 in Excel 2003. Mix ranges, single cells, and literal numbers: =AVERAGE(A1:A10, B5, 100, C1:C5). All numeric values get flattened into one list, then averaged.
Why does AVERAGE return DIV/0?
Because the range contains no numeric values — either all blank, all text, or all errors. AVERAGE = sum ÷ count, and count is zero. Wrap in IFERROR: =IFERROR(AVERAGE(range), 0).
Does AVERAGE work in Google Sheets?
Yes, identically. Same syntax and same behavior across Google Sheets, LibreOffice, and Apple Numbers. AVERAGE is the most portable function in spreadsheet software.
What's the maximum number of arguments AVERAGE accepts?
255 in Excel 2007 and later. 30 in Excel 2003 and earlier. If you hit the limit, you're probably passing individual cells that could be combined into a range — =AVERAGE(A1:A1000) counts as ONE argument.
How do I average only positive numbers?
Use AVERAGEIF with a threshold: =AVERAGEIF(range, ">0"). Similarly "<0" for negatives only. If the range being tested is also the range being averaged, you can omit the third argument.
Should I always use AVERAGE for "the average"?
Not necessarily. Report AVERAGE for symmetric data (test scores, temperatures, measurements). Report MEDIAN for skewed data (incomes, response times). For extreme outlier resistance, consider TRIMMEAN — averages after excluding the top and bottom N%.
Which templates use AVERAGE?
Every one of our 10 templates uses AVERAGE somewhere — it's that universal. Prominently: KPI Dashboard (metric averages), Sales Dashboard (avg deal size), Budget Tracker (avg spending per category), Timesheet (avg hours per project), Loan Calculator (avg monthly interest), plus all the others in more targeted ways.
Templates that use AVERAGE
All 10 templates use AVERAGE somewhere — the most universally-deployed function in the collection:
Skip the syntax. Ask in plain English.
The Sheets & Cells AI Add-in writes AVERAGE, weighted averages, and MEDIAN-vs-AVERAGE comparisons — right inside Excel. Type "average sales excluding outliers" and get the working formula, ready to paste.
Try the AI Add-in →