# How to average a range that contains errors in Excel

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

**Excel:** `=AVERAGE(IFERROR(D2:D7,""))` (Every current version)

**Google Sheets:** `=ARRAYFORMULA(AVERAGE(IFERROR(D2:D7,"")))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Campaign | Spend | Leads | Cost per lead |  | Average cost per lead |
| Search | 1200 | 48 | =B2/C2 |  |  |
| Social | 850 | 0 | =B3/C3 |  |  |
| Email | 300 | 25 | =B4/C4 |  |  |
| Display | 600 | 0 | =B5/C5 |  |  |
| Video | 1500 | 40 | =B6/C6 |  |  |
| Podcast | 900 | 30 | =B7/C7 |  |  |

Formula in G1: `=AVERAGE(IFERROR(D2:D7,""))`

Result: $26.13

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

## Other ways

- `=AVERAGEIF(D2:D7,"<9.99E+307")`: Works in every version without array entry: the condition matches every number and no error.
- `=AVERAGE(IFERROR(D2:D7,0))`: 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.

## 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,""))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/average-ignoring-errors