Excel / Libre Office Calc: среднее значение умножения

Question

У меня есть три ряда в Libre Office

Weight:       0,2  0,2  0,3  0,3
Max. Points:  2    3     4   5
Points:       2    ---   4   4      0,74

Для каждого столбца это количество Points делится на Max. Points а затем умножить на Weight , который сам делится на сумму всех Weight с. Однако, если ячейка в Points является текстовой (а не числовой), ее следует отбросить в расчете и не устанавливать в 0. Здесь должно быть так, как будто второго столбца не было. Наконец я хочу взять среднее.

Я получил это далеко:

SUMPRODUCT(K23:N23;POWER(K22:N22;-1);K21:N21/SUM(K21:N21))

Но это не подсчет среднего, а процент. Также я не смог объединить тот факт, что он игнорирует текст в Points . Один из способов идти об этом было бы установить Weight для Шрифтом- Points 0. Я мог бы использовать IF и ISNUMBER , но позже не только строки, но только ячейки. Я не уверен, как это должно выглядеть.

Например (на этот раз с десятичной нотацией США)

Weight:       0.1  0.2  0.3  0.4
Max. Points:  2    3     4   5
Points:       1   ---    3   4      0.74375

вот что я хочу:

0.74375 = 1/2*(0.1/(0.1+0.3+0.4)) + 3/4*(0.3/(0.1+0.3+0.4)) + 4/5*(0.4/(0.1+0.3+0.4))

Answer 1

Хорошо, вот что я придумал:

=SUMPRODUCT(K23:N23,POWER(K22:N22,-1),K21:N21/SUM(K21:N21),ISNUMBER(K23:N23)) / SUMPRODUCT(ISNUMBER(K23:N23))

Я добавил чек ISNUMBER как вы просили. Однако, похоже, что это не имеет никакого значения - SUMPRODUCT уже проигнорировал столбец L, поскольку он выдал ошибку.

Кроме того, чтобы получить среднее значение, я разделил на SUMPRODUCT(ISNUMBER(K23:N23) . Это просто подсчитывает числовые значения, которых в этом примере 3.

Конечный результат этой формулы равен 0,247 что составляет просто 0,74 / 3 .

РЕДАКТИРОВАТЬ:

Вот формула, которая дает 0.74375 как и ожидалось для второго примера:

=SUMPRODUCT(B3:E3,POWER(B2:E2,-1),B1:E1/SUMPRODUCT(B1:E1,ISNUMBER(B3:E3)),ISNUMBER(B3:E3))

Answer 2

Ваша формула в настоящее время:

Sum(points / maxPoints * weights / sum(weights))

Однако вы можете преобразовать его в эту форму без изменения вывода:

Sum(points / maxPoints * weights) / sum(weights)

С этим вы можете легко рассчитать:

SUMPRODUCT(K23:N23;POWER(K22:N22;-1);K21:N21)/SUMIF(K23:N23;">=0";K21:N21)

Обновить:

Если у вас много строк с "точкой", то вам нужно объединить абсолютные и относительные ссылки для вычисления:

SUMPRODUCT(K23:N23;POWER(K$22:N$22;-1);K$21:N$21)/SUMIF(K23:N23;">=0";K$21:N$21)