How to average a range that contains errors in Excel

Q: Is there a built-in way to ignore errors?

In Excel, =AGGREGATE(1,6,D2:D7) averages while ignoring errors (1 = AVERAGE, 6 = ignore errors).

Q: Should I average the ratios at all?

For cost per lead across campaigns, total spend ÷ total leads (=SUM(B2:B7)/SUM(C2:C7)) is often the better figure, because it weights campaigns by size.

Q: How do I get the maximum or minimum while ignoring errors?

The same pattern: =MAX(IFERROR(D2:D7,"")).

Excel formula

=AVERAGE(IFERROR(D2:D7,""))

Turn the errors into blanks, which AVERAGE skips: =AVERAGE(IFERROR(D2:D7,"")).

Result on the example: $26.13 in G1
Works in: Every current version
Google Sheets: Different formula, see below

See it work

The formula is already in G1. Change any input and the result updates, or edit the formula to experiment.

How it works

A single error makes AVERAGE return that error. IFERROR replaces each error with "", which AVERAGE treats like an empty cell and leaves out of both the total and the count. The four campaigns with leads are averaged: (25 + 12 + 37.50 + 30) ÷ 4.

=AVERAGE(IFERROR(D2:D7,""))

Result26.125

D2:D76 rows × 1 col: $25.00, #DIV/0!, $12.00, #DIV/0!, …
A range: 6 cells in one column.
IFERROR(D2:D7,""){6 rows × 1 col} 25, "", 12, "", …
IFERROR: Returns a fallback when the value is any error.
- valueD2:D76 rows × 1 col: $25.00, #DIV/0!, $12.00, #DIV/0!, …
- value_if_error""not used
AVERAGE(IFERROR(D2:D7,""))26.125
AVERAGE: Arithmetic mean of the numbers.
- number1IFERROR(D2:D7,""){6 rows × 1 col} 25, "", 12, "", …

In Google Sheets

Google Sheets needs a different formula: =ARRAYFORMULA(AVERAGE(IFERROR(D2:D7,""))). On the same data it gives the same result. Here it is running in Google Sheets mode.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=AVERAGEIF(D2:D7,"<9.99E+307")	$26.13	Works in every version without array entry: the condition matches every number and no error.
=AVERAGE(IFERROR(D2:D7,0))	$17.42Different result	Replacing errors with 0 instead. Counts the two campaigns as $0 per lead: 17.42.

Common mistakes

Replacing errors with 0 instead of "" drags the average down, because the zeros are counted.
In Excel 2019 and older, the IFERROR version needs Ctrl+Shift+Enter; AVERAGEIF doesn't.
Fix the source where you can: =IF(C2=0,"",B2/C2) in column D keeps the average formula simple.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Average a range that contains errors

In G1, average the cost per lead in D2:D7, skipping the campaigns that show #DIV/0!.

Questions

Is there a built-in way to ignore errors?

In Excel, =AGGREGATE(1,6,D2:D7) averages while ignoring errors (1 = AVERAGE, 6 = ignore errors).

Should I average the ratios at all?

For cost per lead across campaigns, total spend ÷ total leads (=SUM(B2:B7)/SUM(C2:C7)) is often the better figure, because it weights campaigns by size.

How do I get the maximum or minimum while ignoring errors?

The same pattern: =MAX(IFERROR(D2:D7,"")).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.