# Average – Ignore Errors in Excel & Google Sheets

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

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

1 |
=AGGREGATE(1,6,C3:C5) |

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.

1 |
=AGGREGATE(1,6,C3:C5) |

The Function requires 3 inputs

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

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.

1 |
=AVERAGEIF(B4:D4,">0") |

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