AVERAGEIF(range, criteria) formula that ignores er

问题:

How do you specify the 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(range, criteria) formula something like this:

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

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

I found, via searches, 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. Basically, can you specify in the criteria to ignore errors. For example, I can ignore one error like this:

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

But I don't know how to specify to ignore any errors. So my question is a criteria question. I'm using Microsoft Excel 2013.

回答1:

This will work as long as there aren't any negative numbers in your data:

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

回答2:

Providing you are using Excel 2010 and above the AGGREGATE function can be optioned to ignore all errors.

=AGGREGATE(1, 6, A1:A5)