Формула AVERAGEIF (диапазон, критерии), которая игнорирует ошибки в данных

Как вы определяете критерии, если вы хотите усреднить диапазон чисел, но хотите игнорировать ошибки с помощью функции AVERAGEIF()?

Например, учитывая приведенные ниже данные:

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

Я хочу использовать формулу AVERAGEIF (диапазон, критерии) примерно так:

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

Есть ли способ указать критерии для исключения ошибок? Ответ, который я ожидаю от диапазона, равен 2, среднее значение трех действительных чисел (1, 5 и 0).

Я нашел через поиск, что есть способ сделать это, используя формулу массива или другие функции, но я хочу узнать, есть ли способ сделать это без формулы массива или с помощью другой функции. В принципе, вы можете указать в критериях игнорировать ошибки. Например, я могу игнорировать одну ошибку следующим образом:

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

Но я не знаю, как указать игнорировать любые ошибки. Поэтому мой вопрос - это вопрос критериев. Я использую Microsoft Excel 2013.

Ответ 1

Это будет работать до тех пор, пока в ваших данных не будет никаких отрицательных чисел:

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

Ответ 2

Если вы используете Excel 2010 и выше Функция AGGREGATE, можно игнорировать все ошибки.

=AGGREGATE(1, 6, A1:A5)

Ответ 3

Еще один вариант - использовать AVERAGEIFS, исключая конкретные ошибки.

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

ОДНАКО, я бы настоятельно рекомендовал вместо этого обходного пути решить реальную проблему наличия ошибок в ваших данных.

Например, где у вас есть # DIV/0! error, вставьте IF, чтобы проверить ноль, и, если он найдет, замените ошибку пустой строкой:

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

AVERAGE автоматически игнорирует эти строки.

Не зная больше о том, что вызывает ваши ошибки, я не могу сделать более конкретные предложения по их устранению.