Como calcular a média ponderada no excel?

Uma média é a melhor maneira de obter uma visão geral dos dados. Mas às vezes a média fornece um valor tendencioso. Nessas situações [sempre], a melhor forma é calcular a média ponderada.

E descobri que a melhor maneira é usar uma fórmula combinando as funções SUMPRODUCT e SUM.

Este método é simples e fácil de aplicar. Então, hoje neste artigo, gostaria de compartilhar com vocês como calcular a média ponderada no Excel usando SUMPRODUCT e SUM.

Diferença entre média normal e ponderada

No exemplo abaixo, temos 1.658 como média normal e 1.445 como média ponderada. Deixe-me esclarecer essa diferença em dois pontos.

• Primeiro: se você multiplicar 1.658 (média) por 68 (quantidade), obterá 112.742, que não é igual ao valor total. Mas se você multiplicar 1.445 (média ponderada) por 68 (quantidade), obterá 98.289, que é igual ao valor total.
• Segundo: o Produto 1 tem o preço mais baixo e a maior quantidade, enquanto o Produto 4 tem o preço mais alto e a menor quantidade.

No cálculo da média normal você não leva em consideração a quantidade e se houver alteração na quantidade não haverá efeito no preço médio. E, na média ponderada, você pode considerar a quantidade como peso.

Passo para calcular a média ponderada no Excel

Para calcular a média ponderada, usamos a mesma tabela de dados que mostrei no exemplo acima.

Agora siga estes dois passos simples.

• Na célula C7, insira a seguinte fórmula: =SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6).

• Clique OK.

Agora você tem 1.445 como a média ponderada de preço e produto. Como eu disse, SUMPRODUCT é uma forma simples de calcular média ponderada ou média ponderada no Excel. A melhor parte do SUMPRODUCT é que ele pode multiplicar e adicionar um array em uma única célula.

 =SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)

Como é que isso funciona

Precisamos quebrar essa fórmula para entendê-la.

Primeiro, SUMPRODUCT calculará o produto do preço e da quantidade de todos os produtos e retornará a soma de todos eles. Depois disso, algumas funções fornecerão a soma da quantidade. E no final você obterá a média ponderada dividindo os valores das duas funções.

Confira: SUMPRODUTIVO

Calcule a média móvel ponderada

Vamos um pouco mais longe. Vamos passar para a parte de análise de dados. Usando a mesma fórmula, você também pode calcular a média móvel ponderada.

Aqui estão as etapas:

• Insira a fórmula abaixo na célula D4 e arraste-a até o final. =SOMAPRODUTO(B2:B4,C2:C4)/SOMA(C2:C4)

• Depois de arrastar esta fórmula, ela calculará a média móvel com base em 3 meses para cada mês.

Como é que isso funciona

Se você verificar o instantâneo acima na célula D4, terá a média móvel de janeiro, fevereiro e março. E, na célula D5, você tem a média móvel de fevereiro, março e abril.

Assim, toda vez que você passar para um novo mês, obterá a média móvel, incluindo o mês atual e os últimos dois meses. Basta tornar as referências de células relativas ao arrastar sua fórmula.