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的每一列，其本身除以所有s 的总和。但是，如果 中的单元格是文本（不是数字），则应在计算中丢弃它，而不是将其设置为 0。这里应该像没有第二列一样。最后我想取平均值。Max. PointsWeightWeightPoints

我到目前为止：

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

您的当前公式是：

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)

更新：

如果有许多包含“point”的行，那么您需要结合绝对引用和相对引用来计算：

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

Answer 2

好的，这就是我想出的：

=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))