Média ponderada no Excel: fórmula com SOMARPRODUTO

=SOMARPRODUTO(B2:B5;C2:C5)/SOMA(C2:C5) calcula uma média ponderada: cada nota em B é multiplicada pelo seu peso em C, os produtos são somados e o total é dividido pela soma dos pesos. A SOMARPRODUTO e a SOMA se chamam SUMPRODUCT e SUM em inglês; a tabela mostra as fórmulas em inglês, com vírgulas, e também aceita a forma em português, com ponto e vírgula.

FórmulasRestaurar

F2fx

=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)

	A	B	C	D	E	F
1	Part	Score	Weight		Average	Result
2	Homework	85	20%		Weighted	81.2
3	Quizzes	78	30%		Plain AVERAGE	80.75
4	Midterm	72	20%
5	Final	88	30%

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.No Excel em português: =SOMARPRODUTO(B2:B5;C2:C5)/SOMA(C2:C5)

A nota ponderada é 81.2, enquanto uma MÉDIA (AVERAGE em inglês) simples dá 80.75, porque trata os trabalhos de casa, que valem 20%, como se contassem tanto quanto a prova final, que vale 30%. Mude a nota da prova final e a nota ponderada se move mais do que se move com a mesma mudança nos trabalhos de casa.

O Excel não tem uma função de média ponderada, então a SOMARPRODUTO dividida pela SOMA é a fórmula padrão. O Google Planilhas tem AVERAGE.WEIGHTED(B2:B5,C2:C5).

Como funciona a fórmula da média ponderada

A SOMARPRODUTO multiplica os dois intervalos linha a linha e soma os resultados. Escrita com uma coluna auxiliar, ela é uma coluna de produtos e a SOMA deles:

FórmulasRestaurar

D6fx

=SUM(D2:D5)

	A	B	C	D
1	Part	Score	Weight	Score x weight
2	Homework	85	20%	17.0
3	Quizzes	78	30%	23.4
4	Midterm	72	20%	14.4
5	Final	88	30%	26.4
6	Total		100%	81.2
7	Weighted average			81.2

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.No Excel em português: =SOMA(D2:D5)

Cada parte contribui com a nota vezes o peso: 85 × 20% dá 17,0, 78 × 30% dá 23,4, e assim por diante. Somados, dão 81,2. Os pesos somam 100%, então dividir por C6 não muda nada aqui, mas é isso que mantém a fórmula certa quando eles não somam.

Pesos que não somam 100%

Os pesos não precisam ser porcentagens. Uma média de curso é ponderada por créditos, um preço médio pela quantidade. Dividir pela SOMA dos pesos funciona com qualquer total.

FórmulasRestaurar

F2fx

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

	A	B	C	D	E	F
1	Course	Grade points	Credits		Average	Result
2	Math	4	4		Weighted GPA	3.51
3	History	3	3		Plain average	3.48
4	Biology	3.7	4		Total credits	14
5	Art	2.7	2
6	Lab	4	1

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.No Excel em português: =SOMARPRODUTO(B2:B6;C2:C6)/SOMA(C2:C6)

Sem a divisão, a fórmula retornaria a soma das notas vezes os créditos, 49,2 aqui, e não uma média. Com ela, F2 mostra a média ponderada pelos 14 créditos. As disciplinas de quatro créditos puxam a média para as suas notas, e o laboratório de um crédito quase não a move: mude B6 para 2 e veja como F2 muda pouco em comparação com F3.

Se os seus pesos são porcentagens que somam exatamente 100%, =SOMARPRODUTO(B2:B5;C2:C5) sozinha dá o mesmo resultado. Mantenha o /SOMA(...) mesmo assim: no dia em que um peso mudar e o total virar 105%, a fórmula sem a divisão fica errada e nada na planilha avisa.

Média ponderada com uma condição

Para ponderar só algumas linhas, multiplique por uma condição dentro da SOMARPRODUTO e some os pesos correspondentes com a SOMASE. Abaixo, o preço médio por região é ponderado pela quantidade vendida.

FórmulasRestaurar

G2fx

=SUMPRODUCT((A2:A7=F2)*C2:C7*D2:D7)/SUMIF(A2:A7,F2,D2:D7)

	A	B	C	D	E	F	G
1	Region	Product	Price	Qty		Region	Average price
2	North	Apple	$1.20	100		North	$1.45
3	South	Pear	$1.50	40		South	$1.36
4	North	Pear	$1.50	60
5	South	Apple	$1.20	120
6	North	Plum	$2.00	40
7	South	Plum	$2.00	20

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.No Excel em português: =SOMARPRODUTO((A2:A7=F2)*C2:C7*D2:D7)/SOMASE(A2:A7;F2;D2:D7)

North vendeu 100 maçãs, 60 peras e 40 ameixas, então o preço médio é $1.45, mais perto do preço da maçã do que uma média simples dos três preços ficaria. A condição (A2:A7=F2) vale 1 nas linhas de North e 0 nas outras, então as outras linhas não somam nada em cima, e a SOMASE soma só as quantidades de North embaixo.

Prática: preço médio ponderado

FórmulasRestaurar

F2fx

	A	B	C	D	E	F
1	Batch	Price	Qty		Average	Result
2	Jan	$4.20	100		Weighted price
3	Feb	$4.50	40
4	Mar	$3.90	250
5	Apr	$4.80	10
6	May	$4.10	120

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.

Sua vez: Você comprou o mesmo item em cinco lotes, a preços diferentes. Calcule o preço médio por unidade, ponderado pela quantidade de cada lote. Escreva a fórmula em F2.

VerificarVer resposta

Erros que dão a média ponderada errada

• MÉDIA dos produtos. =MÉDIA(D2:D5) sobre uma coluna de nota × peso divide pelo número de linhas, não pelos pesos, e dá um número pequeno e sem sentido. Divida a SOMA dos produtos pela SOMA dos pesos.
• Dividir pela contagem em vez dos pesos. =SOMARPRODUTO(B2:B6;C2:C6)/CONT.NÚM(B2:B6) só está certa quando todos os pesos são 1.
• Intervalos desalinhados. =SOMARPRODUTO(B2:B6;C3:C7) junta cada valor com o peso da linha seguinte. Os dois intervalos precisam começar e terminar nas mesmas linhas; tamanhos diferentes retornam #VALOR! (#VALUE! em inglês; a tabela mostra os nomes de erro em inglês).
• Um peso em branco. Um peso vazio vale 0, então essa linha fica de fora sem aviso. Se um peso que falta deve impedir o cálculo, confira antes com =CONTAR.VAZIO(C2:C6).
• Média de médias. Duas médias de turma, 70 (10 alunos) e 90 (30 alunos), não dão média 80. Ponderadas pelo tamanho das turmas, o resultado é 85; a página da MÉDIASE mostra a mesma armadilha com condições.