Найти среднее значение, игнорируя пустые ячейки, но учитывая, что ячейки имеют # N/A со значением 100%

Question

Я пытаюсь найти в среднем 3 клетки, но некоторые из них пусты. Мне нужно, чтобы пустые клетки были исключены из среднего.

Есть несколько ячеек с # N/A сообщением об ошибке. Эти клетки должны рассматриваться как 100% значение.

Это самый близкий я дошел до сих пор.

=Averageif(C8:E8,">=0")

=Sumif(C8:e8,"<>#N/A")

Answer 1

Если у вас Excel 2013 или более поздняя версия , вы можете использовать функцию IFNA :

=SUM(IFNA(A1:C1,1))/COUNTA(A1:C1)

COUNTA также будет считать ячейку #N/A

Так как это формула массива, вам нужно "подтвердить" ее, удерживая Ctrl + Shift при нажатии Enter. Если вы сделаете это правильно, Excel поместит фигурные скобки {...} вокруг формулы, как показано на панели формул

Answer 2

Используйте AGGREGATE

=AGGREGATE(1,7,A1:C1)

1 для среднего, а 7 говорит ему игнорировать ошибки.

Answer 3

Самое простое решение, которое я смог найти, это:

=(AGGREGATE(9,6,C4:E4)+COUNTIF(C4:E4,NA()))/COUNTIF(C4:E4,"<>")

где:

• AGGREGATE(9,6,C4:E4) вычислить сумму числовых значений
• COUNTIF(C4:E4,NA()) подсчитывает ячейки с #N/A (так как подсчитать их стоит 1 это просто прекрасно)
• COUNTIF(C4:E4,"<>") подсчитывает непустые ячейки