Promedio ponderado en Excel: fórmula con SUMAPRODUCTO

=SUMAPRODUCTO(B2:B5;C2:C5)/SUMA(C2:C5) calcula un promedio ponderado: cada nota de B se multiplica por su peso de C, los productos se suman y el total se divide entre la suma de los pesos. En inglés las funciones se llaman SUMPRODUCT y SUM, y la tabla muestra la fórmula así: =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5). La tabla también acepta las fórmulas escritas en español, con punto y coma (o con comas, como en México).

FórmulasRestablecer

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%

Haz clic en una celda para ver su fórmula. Cambia un número o una fórmula y la hoja se recalcula.En Excel en español: =SUMAPRODUCTO(B2:B5;C2:C5)/SUMA(C2:C5)

La nota ponderada es 81,2, mientras que un PROMEDIO simple da 80,75, porque trata las tareas, que valen un 20%, como si contaran tanto como el examen final, que vale un 30%. Cambia la nota del final y la nota ponderada se mueve más de lo que se movería con el mismo cambio en las tareas.

Excel no tiene una función PROMEDIO.PONDERADO, así que SUMAPRODUCTO dividido entre SUMA es la fórmula estándar. Google Sheets tiene AVERAGE.WEIGHTED(B2:B5,C2:C5).

Cómo funciona la fórmula del promedio ponderado

SUMAPRODUCTO multiplica los dos rangos fila por fila y suma los resultados. Escrito con una columna auxiliar, es una columna de productos y su SUMA:

FórmulasRestablecer

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

Haz clic en una celda para ver su fórmula. Cambia un número o una fórmula y la hoja se recalcula.En Excel en español: =SUMA(D2:D5)

Cada parte aporta su nota por su peso: 85 × 20% es 17,0, 78 × 30% es 23,4, y así sucesivamente. Suman 81,2. Los pesos suman 100%, así que dividir entre C6 no cambia nada aquí, pero es lo que mantiene la fórmula correcta cuando no suman 100%.

Pesos que no suman 100%

Los pesos no tienen por qué ser porcentajes. Un promedio de calificaciones (GPA) se pondera por horas de crédito, y un precio promedio por cantidad. Dividir entre la SUMA de los pesos sirve para cualquier total.

FórmulasRestablecer

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

Haz clic en una celda para ver su fórmula. Cambia un número o una fórmula y la hoja se recalcula.En Excel en español: =SUMAPRODUCTO(B2:B6;C2:C6)/SUMA(C2:C6)

Sin la división, la fórmula devolvería la suma de los puntos por los créditos, aquí 49,2, no un promedio. Con ella, F2 muestra el promedio ponderado por 14 créditos. Los cursos de cuatro créditos acercan el promedio a sus notas, y el laboratorio de un crédito apenas lo mueve: cambia B6 a 2 y mira lo poco que cambia F2 en comparación con F3.

Si tus pesos son porcentajes que suman exactamente 100%, =SUMAPRODUCTO(B2:B5;C2:C5) solo da el mismo resultado. Conserva de todos modos el /SUMA(...): el día que se cambie un peso y el total pase a 105%, la fórmula sin él estará mal y nada en la hoja lo dirá.

Promedio ponderado con una condición

Para ponderar solo algunas filas, multiplica por una condición dentro de SUMAPRODUCTO y suma los pesos que coinciden con SUMAR.SI. Abajo, el precio promedio de cada región se pondera por la cantidad vendida.

FórmulasRestablecer

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

Haz clic en una celda para ver su fórmula. Cambia un número o una fórmula y la hoja se recalcula.En Excel en español: =SUMAPRODUCTO((A2:A7=F2)*C2:C7*D2:D7)/SUMAR.SI(A2:A7;F2;D2:D7)

North vendió 100 manzanas, 60 peras y 40 ciruelas, así que su precio promedio es $1.45, más cerca del precio de la manzana de lo que estaría un promedio simple de los tres precios. La condición (A2:A7=F2) vale 1 en las filas de North y 0 en las demás, así que las otras filas no aportan nada arriba, y SUMAR.SI suma solo las cantidades de North abajo.

Práctica: precio promedio ponderado

FórmulasRestablecer

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

Haz clic en una celda para ver su fórmula. Cambia un número o una fórmula y la hoja se recalcula.

Tu turno: Compraste el mismo artículo en cinco lotes a precios distintos. Calcula el precio promedio por unidad, ponderado por la cantidad de cada lote. Escribe la fórmula en F2.

ComprobarVer respuesta

Errores que dan un promedio ponderado equivocado

• PROMEDIO de los productos. =PROMEDIO(D2:D5) sobre una columna de nota × peso divide entre el número de filas, no entre los pesos, y da un número pequeño sin sentido. Divide la SUMA de los productos entre la SUMA de los pesos.
• Dividir entre el conteo en lugar de los pesos. =SUMAPRODUCTO(B2:B6;C2:C6)/CONTAR(B2:B6) solo es correcto cuando todos los pesos son 1.
• Rangos que no están alineados. =SUMAPRODUCTO(B2:B6;C3:C7) empareja cada valor con el peso de la fila siguiente. Los dos rangos deben empezar y terminar en las mismas filas; con tamaños distintos devuelven #¡VALOR! (#VALUE! en inglés; la tabla muestra los nombres de error en inglés).
• Un peso en blanco. Un peso vacío cuenta como 0, así que esa fila queda fuera sin avisar. Si un peso que falta debe detener el cálculo, compruébalo antes con =CONTAR.BLANCO(C2:C6).
• Promediar promedios. Dos promedios de clase de 70 (10 alumnos) y 90 (30 alumnos) no dan 80 de promedio. Pondéralos por el tamaño de las clases y el resultado es 85; la página de PROMEDIO.SI tiene la misma trampa con condiciones.