Average Ignore Errors – Excel & Google Sheets

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

average ignore errors Main Function

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.

Average

In some cases, this may be the desired result. An error value can show you there needs to be an adjustment or correction in your source data. In other cases, it’s fine for your data to include error values, but you want to average the rest of the set.

Below, we cover the case where the dataset may contain errors and you wish to exclude those values from your average. To ignore cells with errors, we can use the AGGREGATE or AVERAGEIF functions.

Ignore Errors With AGGREGATE Function

To ignore cells with error values when performing a calculation of an average, the most robust way is to use Excel’s AGGREGATE Function:

AGGREGATE

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.

AutoMacro - VBA Code Generator

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.

AVERAGEIF

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

 

 

 

Excel Practice Worksheet

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

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!