AVERAGEIF formula (range, criteria) that ignores data errors

Question

How do you define criteria if you want to average a range of numbers, but want to ignore errors using the AVERAGEIF () function?

For example, given the data below:

A1: 1 A2: #DIV/0! A3: #VALUE! A4: 5 A5: 0 

I want to use the AVERAGEIF formula (range, criteria) something like this:

 =AVERAGEIF(a1:a5,and("<>#DIV/0!","<>#VALUE!")) or =AVERAGEIF(a1:a5,"<>Error()") 

Is there a way to specify criteria for eliminating errors? The answer I expect from the range is 2, the average of three real numbers (1, 5, and 0).

I found through a search that there is a way to do this using an array formula or other functions, but I want to find out if there is a way to do this without an array formula or using another function. In principle, you can specify in the criteria to ignore errors. For example, I can ignore one error as follows:

 AVERAGEIF(a1:a5,"<>#DIV/0!") 

But I do not know how to indicate to ignore any errors. So my question is a question of criteria. I am using Microsoft Excel 2013.

Answer 1

This will work until there are no negative numbers in your data:

 =AVERAGEIF(A1:A5,">=0") 

Answer 2

If you use Excel 2010 and above the AGGREGATE function , you can ignore all errors.

 =AGGREGATE(1, 6, A1:A5) 

Answer 3

Another option is to use AVERAGEIFS, excluding specific errors.

 =AVERAGEIFS(A13:A17,A13:A17,"<>#DIV/0!",A13:A17,"<>#VALUE!") 

HOWEVER, I would highly recommend instead of this workaround to solve the real problem of having errors in your data.

For example, where you have # DIV / 0! error, insert IF to check for zero, and if it finds one, replace the error with an empty line:

 =IF(X=0,"",Y) 

AVERAGE automatically ignores these lines.

Without knowing more about what causes your errors, I cannot make more specific suggestions for resolving them.