Формула Excel, чтобы получить% случаев со средним значением ниже 5

Question

Я ищу построить формулу, чтобы подсчитать, сколько раз среднее значение будет ниже числа 5. Чтобы задать вопрос:

_|__________A__________|__B___|__C___|____D______
1| PRODUCTS            | WEB1 | WEB2 | TELEPHONE
2| MORE THAN 1 PRODUCT |      |      |    1
3| INSURANCE           |      |      |    3
4| RRVV                |      |      |    5
5| PPP                 |      |      |    7
6| INSURANCE           |  4   |   5  |  
7| MMHH                |  3   |   3  |
8| INSURANCE           |  3   |   7  |  

Что мне нужно, так это получить% страховых случаев, которые имеют в среднем менее 5 в одной формуле, чтобы получить общую сумму.

В данном конкретном случае есть 3 строки, соответствующие Критериям продукта (Страхование), но только у 2 из них среднее значение меньше 5 (последняя строка ровно 5). Таким образом, ответ должен быть 67% (66,6666...)

Я пробовал графинты, суффиксы, суппродукты и т. Д.

Answer 1

=(SUM((1/MMULT((B2:D8<>"")*1,TRANSPOSE(B1:D1<>"")*1)*MMULT(B2:D8*1,TRANSPOSE(B1:D1<>"")*1)*(A2:A8="insurance")<5)*1)-COUNTIF(A2:A8,"<>insurance"))/COUNTIF(A2:A8,"insurance")

Подтвердите, нажав Ctrl+ Shift+ Enter.

Это не может быть оптимальным решением, но должно работать.

Answer 2

Используйте вспомогательный столбец. В E2 положить следующую формулу массива:

=AVERAGE(IF(A2="INSURANCE",IF(ISNUMBER(B2:D2),B2:D2)))

Будучи массивом после размещения его в строке формул, нажмите Ctrl-Shift-Enter вместо Enter.

Тогда вы можете использовать следующую формулу:

=COUNTIFS(E2:E8,"<"&5,E2:E8,">"&0)/COUNTIF(E2:E8,">"&0)