How to average a range that contains errors

✓ Verified in LibreOffice 25.8.7.3 Google Sheets returned something else (2026-08-30)

Get a mean even when some cells are #N/A or #DIV/0! — without deleting them first.

The formula

App	Formula	Notes
Excel (desktop)	`=AGGREGATE(1,6,A2:A6)`	Function 1 = AVERAGE, option 6 = ignore errors. Excel/LibreOffice; not in Google Sheets.
Google Sheets	`=AVERAGE(FILTER(A2:A6,ISNUMBER(A2:A6)))`	Google Sheets has no AGGREGATE — executed 2026-08-30, =AGGREGATE(1,6,A2:A6) returned #NAME? there against 20 in LibreOffice. FILTER keeps only the numeric cells, so error cells drop out before AVERAGE sees them; =AVERAGEIF(A2:A6,"<>#N/A") handles #N/A alone. Documented Sheets syntax, carrying no executed result of its own.
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 filter the errors out with =AVERAGE(FILTER(A2:A6,ISNUMBER(A2:A6))) — an IFERROR wrap needs ARRAYFORMULA around it in Sheets to iterate over the range. 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.

The Google Sheets alternative

Google Sheets needs different syntax for this task. The formula below is Sheets-specific — it was executed in Google Sheets (2026-08-30) against the same sample data as the worked example, and the value beside it is what Google returned. The LibreOffice column reads n/a because the formula is outside LibreOffice’s dialect, so there is nothing of ours to report there.

Formula	What it does	Returned by LibreOffice 25.8.7.3	Returned by Google Sheets (executed 2026-08-30)
`=AVERAGE(FILTER(A2:A6,ISNUMBER(A2:A6)))`	FILTER + ISNUMBER drops the error cells before AVERAGE sees them — Google Sheets has no AGGREGATE	n/a (Sheets-only formula)	20 Google Sheets alternative (executed 2026-08-30) NOTE: written with the PLAIN function name; the LibreOffice reference run executed the _xlfn. storage form of this formula, so the two runs are not byte-identical inputs

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. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. For the worked example Google Sheets returned #NAME?, which is not what LibreOffice returned (20) — both values are shown as each engine produced them, and the disagreement itself is the finding. A further 1 row is a Google Sheets alternative: Sheets-specific syntax, executed in Google Sheets only, so the LibreOffice column reads n/a for it. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.

Functions used

AGGREGATE · AVERAGE · FILTER · ISNUMBER — see full Excel, Google Sheets & LibreOffice compatibility for each.