Average – Ignore Errors in Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on February 8, 2023
Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to calculate the average of a set of numbers, excluding error values, in Excel and Google Sheets.

average ignore errors


Error Issues With the AVERAGE Function

The AVERAGE Function calculates the mathematical mean of a set of numbers. However, when the input range includes an error value, the AVERAGE Function will output an error.


In some cases, this may be the desired result. An error value can alert you to problems with your data. In other cases, it’s fine for your data to include error values, but you want to average the rest of the data.

To ignore cells with errors, we can use the AGGREGATE or AVERAGEIF functions.

Ignore Errors With AGGREGATE Function

To ignore cells with error values use Excel’s AGGREGATE Function:



The AGGREGATE Function allows you to use any of several mathematical summary functions, including AVERAGE, but with additional options on how the input data is processed. In this example, we utilize the option to ignore inputs with errors.


The Function requires 3 inputs

  1. function number – This is the calculation to perform. Use 1 for AVERAGE.
  2. options – To ignore error values in inputs, we use option 6.
  3. input range – The range to calculate.

Visit our AGGREGATE Function page to learn more about the options available.

Ignore Errors With AVERAGEIF Function

The AVERAGEIF Function can also be used to ensure that only specific numerical values are used in the calculation. Here we use “>0” to average only numbers greater than zero. This also eliminates any errors.



Ignore Errors With AVERAGE Function in Google Sheets

The AVERAGEIFS Function works in exactly the same way in Google Sheets as in Excel. However, the AGGREGATE Function is not available in Google Sheets.

average ignore errors Google Function

AI Formula Generator

Try for Free

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Return to Excel Formulas List