Average But Ignore Errors (#N/A and Other Errors)

In Excel, when you try to average values from a range, but you have an error in one or more cells, your formula will return an error in the result. In this tutorial, we will learn to average values from a range but ignore the error values at the same time.

Ignore #N/A Error

In the following example, we have an #N/A error in the range of cells, and when you use the AVERAGE function it returns #N/A errors in the result as well.

#na-error-in-cell

To deal with this situation, you can use the AVERAGEIF function in the following way.

averageif-function
=AVERAGEIF(A1:A10,"<>#N/A")

AVERAGEIF allows you to create a condition to average values from a range. And in this formula, we have referred to the range A1:A10, and after that, we have used a condition that averages values that are not equal to #N/A.

Using AVERAGEIF to Ignore All the Errors

If you want to ignore all the errors, you can still use AVERAGEIF. You just need to change the criteria in the function.

averageif-to-ignore-errors

In this formula, we have used greater than 0 in the criteria. When you do this, Excel only refers to the numbers from the range and averages only those cells with numbers.

Use AGGREGATE to Ignore Errors while Averaging Values

AGGREGATE is a new function that can help you to average values, but with this function, you have the option to ignore errors from the range.

aggregate-to-ignore-errors

In the first argument of the function, you need to use the function number. This means if you want to average values, select the function from the list.

first-argument-of-aggregate-function

After that, in the second argument, select the option to ignore the errors, or just enter 6.

select-options-to-ignore-errors

Now, in the third argument, refer to the range where you have values to average.

refer-to-range

In the end, hit enter to get the result in the cell.

Download Sample File