How to average a range that contains errors
✓ Verified in LibreOffice 25.8.7.3Get a mean even when some cells are #N/A or #DIV/0! — without deleting them first.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =AGGREGATE(1,6,A2:A6) | Function 1 = AVERAGE, option 6 = ignore errors. Excel/LibreOffice; not in Google Sheets. |
| Google Sheets | =AVERAGEIF(A2:A6,"<>#N/A") | Sheets has no AGGREGATE. For all error types: =AVERAGE(IFERROR(A2:A6,"")) as an array, or FILTER out errors first. |
| LibreOffice Calc | =AGGREGATE(1,6,A2:A6) | Same as Excel. |
How it works
A plain AVERAGE returns an error if any cell in the range is an error. AGGREGATE(1,6,range) averages (function code 1) while ignoring errors (option 6), so it takes the mean of just the three valid numbers: (10+20+30)/3 = 20, skipping the #DIV/0! and #N/A. AGGREGATE is in Excel and LibreOffice but NOT Google Sheets — there, use AVERAGEIF to exclude #N/A, or wrap the range in IFERROR. The same AGGREGATE approach ignores errors for SUM (code 9), MAX (4), MIN (5), COUNT (2) and more — see the sum-a-column-ignoring-errors recipe.
Verified, not just documented
We ran =AGGREGATE(1,6,A2:A6) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 20 — exactly the expected result. Every formula here is confirmed by actually executing it.