Average and Ignore Errors

Excel 2010+AGGREGATE

One #N/A or #DIV/0! in a column makes a plain AVERAGE return an error too. AGGREGATE averages straight through the errors — no cleanup, no helper column.

Quick formula: to average B2:B8 while skipping any error cells:

=AGGREGATE(1, 6, B2:B8)

The first argument 1 means AVERAGE; the second 6 means “ignore errors.”

Functions used (tap for the full reference guide):

AGGREGATE IFERROR AVERAGE

The example

A column where one calculation errored. AGGREGATE averages the rest.

	A	B
1	Item	Value
2	A	100
3	B	(error)
4	C	140
5	D	120
6	Average (no errors):	120

The formula

The error-proof average:

=AGGREGATE(1, 6, B2:B5) // averages 100,140,120 → 120

How it works

AGGREGATE is a Swiss-army function; two arguments configure it here:

Argument 1 is the function number — 1 is AVERAGE (9 is SUM, 4 is MAX, 5 is MIN, and so on).
Argument 2 is the options — 6 tells it to ignore error values (and 7 also ignores hidden rows).
The third argument is the range. AGGREGATE averages the valid numbers and steps over the error in B3.
A plain =AVERAGE(B2:B5) here would return #N/A — AGGREGATE returns 120.

No AGGREGATE (pre-2010)? Use an array formula: =AVERAGE(IF(ISNUMBER(B2:B8), B2:B8)) entered with Ctrl+Shift+Enter, or fix the source with IFERROR first.

Try it: interactive demo

Live demo

Toggle whether one cell is an error; compare AVERAGE vs AGGREGATE.

Cell B3 is an error

AVERAGE: AGGREGATE:

Variations

Sum ignoring errors

Use function number 9 (SUM):

=AGGREGATE(9, 6, B2:B8)

Max / min ignoring errors

4 is MAX, 5 is MIN:

=AGGREGATE(4, 6, B2:B8)

Pre-2010 array version

Ctrl+Shift+Enter:

=AVERAGE(IF(ISNUMBER(B2:B8), B2:B8))

Pitfalls & errors

AGGREGATE needs Excel 2010+. Older versions show #NAME? — use the array AVERAGE(IF(ISNUMBER())) instead.

Option 6 ignores errors, not text. Stray text cells are skipped by AVERAGE anyway, but blank-as-text or zero values still count — clean those separately.

Fixing the source is often better. If the errors come from your own formulas, wrapping them in IFERROR at the source keeps the whole sheet clean, not just the average.

Practice workbook

📊

Download the free Average and Ignore Errors practice workbook

A column with an error, AGGREGATE shown with its result, plus the SUM/MAX variants and the array fallback, and 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I average a range that contains errors in Excel?

Use =AGGREGATE(1, 6, range). Function number 1 is AVERAGE and option 6 ignores error values, so the average computes from the valid numbers only.

How do I sum or take the max ignoring errors?

Change the first AGGREGATE argument: 9 for SUM, 4 for MAX, 5 for MIN, each with option 6, e.g. =AGGREGATE(9, 6, range).

How do I ignore errors without AGGREGATE?

Use an array formula like =AVERAGE(IF(ISNUMBER(range), range)) entered with Ctrl+Shift+Enter, or wrap the source formulas in IFERROR.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: AGGREGATE · IFERROR · AVERAGE