AVERAGE in Excel: The Arithmetic Mean | 5 Examples + AVERAGEA Trap + Weighted Averages | Sheets & Cells
Function · Statistical

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.

✓
Universal support since Excel 1.0
Works identically in Excel 365, 2021, 2019, 2016, 2013, 2010, 2007, 2003, Excel for the web, Google Sheets, LibreOffice, and Apple Numbers. No _xlfn. prefix. The most portable function in Excel — ship it anywhere without worry.
🔍
What AVERAGE ignores — and the one thing it counts
Three cell types get skipped automatically; one very much does not. The zero trap catches everyone at least once: if a "no data" cell contains 0 instead of being blank, AVERAGE will drag your mean toward zero.
Empty cells
( blank )
✓ Ignored
Text values
"N/A", "TBD"
✓ Ignored
Logical values
TRUE, FALSE
✓ Ignored
Zero (0)
0, 0.0, "=0"
⚠ Counted

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.

Quick answer
AVERAGE returns the arithmetic mean of its numeric arguments — sum divided by count of numeric values. Ignores blanks, text, and logical values. Counts zeros.
Syntax
=AVERAGE(number1, [number2], ...)
Working example
=AVERAGE(SalesAmount) → Returns $6,022.22 — the mean of all 18 sales. Sum of 18 sales ($108,400) ÷ 18 rows = $6,022.22
📗 Free AVERAGE + MEDIAN + MIN + MAX workbook
9 sheets · 18-row Q1 2026 sales log · AVERAGE basics · AVERAGEA text quirk · weighted averages · outlier sensitivity vs MEDIAN · skewed-data demo
Download .xlsx (free) Open in Sheets
Category
Statistical
Difficulty
Beginner
Excel version
All (1.0+)
Templates
10 of 10 use it
1985
In Excel since v1.0
255
Max arguments (Excel 2007+)
100%
Cross-version support
10/10
Templates using it

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.

ArgumentTypeWhat 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.
All arguments get flattened. Whether you pass one range of 100 cells or a hundred single cells, AVERAGE treats them identically — sum them all, divide by the count of numeric values found. Order and grouping don't matter.

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.

=AVERAGE(SalesAmount)
→ Returns $6,022.22 — total $108,400 ÷ 18 rows

Partial range

=AVERAGE(E4:E8)
→ Returns $6,180.00 — mean of the first 5 sales (2500+4500+12000+9500+2100)/5

Manually listed cells

=AVERAGE(E4, E7, E12, E17)
→ Returns $5,925.00 — Emma Thompson's 4 sales manually listed

Mixed literals and references

=AVERAGE(SalesAmount, 100000)
→ Returns $10,968.42 — 18 sales PLUS an extra $100,000 literal (foreshadowing Example 4)
FormulaWhat it doesResult
=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 B2Depends 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?

=AVERAGE(G16:G20)
→ Returns 275 — averages the 4 numeric values: (100+200+300+500)/4
=AVERAGEA(G16:G20)
→ Returns 220 — treats "text!" as 0: (100+200+300+0+500)/5
ValueAVERAGE readsAVERAGEA reads
100100100
200200200
300300300
"text!"skipped0 (counted)
500500500
Result →AVG=275, AVGA=220

Also affects TRUE / FALSE

AVERAGE ignores logical values. AVERAGEA counts TRUE as 1 and FALSE as 0.

=AVERAGE(0.5, 0.3, 0.8, TRUE)
→ Returns 0.533 — TRUE is ignored → (0.5+0.3+0.8)/3
=AVERAGEA(0.5, 0.3, 0.8, TRUE)
→ Returns 0.65 — TRUE counts as 1 → (0.5+0.3+0.8+1)/4
99% of the time you want AVERAGE, not AVERAGEA. AVERAGEA is designed for cases where text or booleans meaningfully represent zero — extremely niche. Common misconception: "AVERAGEA is a newer/better version of AVERAGE." It isn't. It's a special-case variant that treats non-numbers as zeros. Reach for AVERAGE by default; use AVERAGEA only when you deliberately want text-as-zero behavior.

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.

RepAvg deal sizeDeals closed
Emma$5,9254
David$5,8004
Michael$8,3003
Sofia$4,2333
Aisha$6,1502
James$5,8002

Simple AVERAGE — every rep counts equally

=AVERAGE(B24:B29)
→ Returns $6,034.67 — treats Aisha (2 deals) the same as Emma (4 deals). Misleading.

Weighted AVERAGE — reps with more deals count more

=SUMPRODUCT(B24:B29, C24:C29) / SUM(C24:C29)
→ Returns $6,022.22 — matches the true team average from the raw sales log

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

Weighted averages are everywhere. Portfolio returns weighted by allocation, student GPA weighted by credit hours, poll results weighted by demographics, product ratings weighted by review count. If you're averaging "averages" from different-sized groups, you almost certainly want SUMPRODUCT ÷ SUM, not a plain AVERAGE.

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.

=AVERAGE(4000, 5000, 6000, 7000, 8000)
→ Returns $6,000 — clean and representative
=MEDIAN(4000, 5000, 6000, 7000, 8000)
→ Returns $6,000 — same result when data is symmetric

Add one outlier

Now add a $100,000 value to the same set:

=AVERAGE(4000, 5000, 6000, 7000, 8000, 100000)
→ Returns $21,666.67 — one outlier tripled the mean
=MEDIAN(4000, 5000, 6000, 7000, 8000, 100000)
→ Returns $6,500 — barely moved

💡 The takeaway

AVERAGE
$6,000 → $21,667
One outlier shifted the mean by 261%. If you reported "average was $21K" people would think everyone earned that much.
MEDIAN
$6,000 → $6,500
Same outlier moved the median by 8%. Half of values are still below $6,500 — the "typical" value is preserved.
Rule of thumb: use AVERAGE for symmetric data (test scores, temperatures, measurements) and MEDIAN for skewed data (incomes, home prices, response times, purchase amounts). If you're unsure, calculate both — if they differ significantly, your data is skewed and MEDIAN is likely the more honest summary. Full deep-dive on the MEDIAN page.

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

=AVERAGEIF(SalesRep, "Michael Chen", SalesAmount)
→ Returns $8,300.00 — Michael's 3 sales averaged

Multiple conditions — AVERAGEIFS

=AVERAGEIFS(SalesAmount, SalesRep, "Emma Thompson", SalesProduct, "Widget*")
→ Returns $3,850.00 — Emma's Widget-only sales

Comparison: plain AVERAGE + manual filter (the hard way)

=AVERAGE(IF(SalesRep="Michael Chen", SalesAmount))
→ Returns $8,300.00 — same result but requires array-formula treatment (Ctrl+Shift+Enter in older Excel)
For conditional averaging, always prefer AVERAGEIF or AVERAGEIFS. They read cleanly, don't need array-formula ceremony, and are just as fast. Full details on the AVERAGEIF and AVERAGEIFS pages — including the crucial DIV/0-on-no-matches trap.

Interactive playground

Try it Live AVERAGE demonstration

Mirrors live cells from the workbook. Edit the yellow input → the blue answer updates.

Input · Range
SalesAmount (18 rows)
Output · Mean
$6,022.22
=AVERAGE(SalesAmount)
Input · Add outlier
$100,000
Output · Skewed mean
$10,968.42
=AVERAGE(SalesAmount, 100000)

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

ResultWhy it happensBroken → 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
📗 Every example above, in one workbook
Basic AVERAGE · AVERAGEA text quirk · weighted averages · outlier sensitivity vs MEDIAN · plus MIN/MAX side-by-side comparisons.
Download basic-aggregates-examples-2026.xlsx

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.

PlatformSupports AVERAGE?Notes
Excel 365 (Windows & Mac)✓ YesFull support · 255 args
Excel 2021 / 2019 / 2016 / 2013 / 2010 / 2007✓ YesFull support · 255 args
Excel 2003✓ Yes30-arg limit
Excel for the web✓ YesFull support
Excel on iPad & iPhone✓ YesFull support
Google Sheets✓ YesSame syntax and behavior
LibreOffice Calc✓ YesFull support
Apple Numbers✓ YesFull support

How to write an AVERAGE from scratch

  1. Point at your range

    The simplest AVERAGE takes one range: =AVERAGE(A2:A100). Named ranges make it read cleaner: =AVERAGE(SalesAmount).

  2. 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.

  3. 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.

  4. Need a condition? Use AVERAGEIF or AVERAGEIFS

    Rather than writing =AVERAGE(IF(...)) as an array formula, use the purpose-built AVERAGEIF or AVERAGEIFS.

  5. 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).

  6. 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

🎁 Grab the free Basic Aggregates workbook
9 sheets covering AVERAGE, MEDIAN, MIN, MAX — with the killer outlier-sensitivity demo and the skewed-income case study.
Download basic-aggregates-examples-2026.xlsx

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 →