Fórmula genérica
=SUMPRODUCT(weights,values)/SUM(weights)
Resumo
Para calcular uma média ponderada, você pode usar a função SUMPRODUCT junto com a função SUM. No exemplo mostrado, a fórmula em G5, copiada, é:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
onde pesos é o intervalo nomeado I5: K5.
Explicação
Uma média ponderada, também chamada de média ponderada, é uma média em que alguns valores contam mais do que outros. Em outras palavras, alguns valores têm mais "peso". Podemos calcular uma média ponderada multiplicando os valores da média pelos pesos correspondentes e, em seguida, dividindo a soma dos resultados pela soma dos pesos. No Excel, isso pode ser representado com a fórmula genérica abaixo, onde pesos e valores são intervalos de células:
=SUMPRODUCT(weights,values)/SUM(weights)
Na planilha mostrada, as pontuações de 3 testes aparecem nas colunas C a E e os pesos estão nos pesos de intervalo nomeados (I5: K5). A fórmula na célula G5 é:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
Trabalhando de dentro para fora, primeiro usamos a função SUMPRODUCT para multiplicar os pesos pelas pontuações correspondentes e somar o resultado:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
SUMPRODUCT primeiro multiplica os elementos correspondentes das duas matrizes, em seguida, retorna a soma do produto:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
O resultado é então dividido pela soma dos pesos:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
Como a fórmula é copiada para baixo na coluna G, os pesos de intervalo nomeados I5: K5 não mudam, uma vez que se comporta como uma referência absoluta. No entanto, as pontuações em C5: E5, inseridas como uma referência relativa, são atualizadas em cada nova linha. O resultado é uma média ponderada para cada nome na lista, conforme mostrado. A média na coluna F é calculada apenas para referência com a função AVERAGE:
=AVERAGE(C5:E5)
Pesos que não somam 1
Neste exemplo, os pesos são configurados para somar 1, então o divisor é sempre 1 e o resultado é o valor retornado por SUMPRODUCT. No entanto, um bom recurso da fórmula é que os pesos não precisam somar 1.
Por exemplo, poderíamos usar um peso 1 para os primeiros dois testes e um peso 2 para o final (já que o final é duas vezes mais importante) e a média ponderada será a mesma:
Na célula G5, a fórmula é resolvida assim:
=SUMPRODUCT(weights,C5:E5)/SUM(weights) =SUMPRODUCT((1,1,2),(90,83,90))/SUM(1,1,2) =SUMPRODUCT((90,83,180))/SUM(1,1,2) =353/4 =88.25
Nota: os valores entre chaves () acima são intervalos expressos como matrizes.
Pesos de transposição
A função SUMPRODUCT requer que as dimensões da matriz sejam compatíveis. Se as dimensões não forem compatíveis, SUMPRODUCT retornará um erro #VALUE. No exemplo abaixo, os pesos são iguais aos do exemplo original, mas estão listados em uma faixa vertical:
Para calcular uma média ponderada com a mesma fórmula, precisamos "inverter" os pesos em uma matriz horizontal com a função TRANSPOSE como esta:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
Após a execução de TRANSPOSE, a matriz vertical:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
torna-se:
=(0.25,0.25,0.5) // horizontal array
E a partir desse ponto, a fórmula se comporta como antes.
Leia mais: arrays verticais e horizontais.
