Excel / Libre Office Calc: Tomando a média de uma multiplicação

Question

Eu tenho três linhas no Libre Office

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

Para cada coluna, Points é dividido por Max. Points e, em seguida, multiplicado por Weight , que é dividido pela soma de todos os Weight s. No entanto, se uma célula em Points for texto (não um número), ela deve ser descartada no cálculo e não deve ser definida como 0. Aqui, deve ser como se não houvesse uma segunda coluna. Finalmente, quero pegar a média.

Cheguei até aqui:

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

Mas isso não é calcular a média, mas uma porcentagem. Também não consegui integrar o fato de que ele ignora o texto em Points . Uma maneira de fazer isso seria definir Weight para o texto- Points para 0. Eu poderia usar IF e ISNUMBER , mas a última não precisa de linhas, mas apenas de células. Não sei como isso deve ser.

Por exemplo (desta vez com a notação decimal dos EUA)

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

aqui está o que eu quero:

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

Sua fórmula atualmente é:

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

No entanto, você pode transformá-lo neste formulário sem alterar a saída:

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

Com isso, você pode calcular facilmente:

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

Atualização:

Se você tiver muitas linhas com "ponto", precisará combinar referências absolutas e relativas para calcular:

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

    

Answer 2

Ok, aqui está o que eu criei:

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

Adicionei a verificação de ISNUMBER conforme solicitado. No entanto, não pareceu fazer nenhuma diferença - SUMPRODUCT já ignorou a coluna L porque produziu um erro.

Além disso, para obter a média, dividi por SUMPRODUCT(ISNUMBER(K23:N23) . Isso simplesmente conta os valores numéricos, dos quais existem 3 neste exemplo.

O resultado final dessa fórmula é 0,247 , que é simplesmente 0,74 / 3 .

EDITAR :

Aqui está uma fórmula que produz 0.74375 como esperado para o segundo exemplo:

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