Fórmula genérica
=SUMPRODUCT(weights,values)/SUM(weights)
Resumen
Para calcular un promedio ponderado, puede utilizar la función SUMPRODUCT junto con la función SUM. En el ejemplo que se muestra, la fórmula en G5, copiada, es:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
donde pesos es el rango nombrado I5: K5.
Explicación
Un promedio ponderado, también llamado media ponderada, es un promedio en el que algunos valores cuentan más que otros. En otras palabras, algunos valores tienen más "peso". Podemos calcular un promedio ponderado multiplicando los valores para promediar por los pesos correspondientes y luego dividiendo la suma de los resultados por la suma de los pesos. En Excel, esto se puede representar con la fórmula genérica a continuación, donde los pesos y los valores son rangos de celda:
=SUMPRODUCT(weights,values)/SUM(weights)
En la hoja de trabajo que se muestra, los puntajes de 3 pruebas aparecen en las columnas C a E, y las ponderaciones están en el rango de ponderaciones con nombre (I5: K5). La fórmula en la celda G5 es:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
Trabajando de adentro hacia afuera, primero usamos la función SUMPRODUCTO para multiplicar los pesos por las puntuaciones correspondientes y sumar el resultado:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
SUMPRODUCT primero multiplica los elementos correspondientes de las dos matrices juntas, luego devuelve la suma del producto:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
Luego, el resultado se divide por la suma de los pesos:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
A medida que la fórmula se copia en la columna G, el rango nombrado pondera I5: K5 no cambia, ya que se comporta como una referencia absoluta. Sin embargo, los puntajes en C5: E5, ingresados como referencia relativa, se actualizan en cada fila nueva. El resultado es un promedio ponderado para cada nombre en la lista como se muestra. El promedio en la columna F se calcula como referencia solo con la función PROMEDIO:
=AVERAGE(C5:E5)
Pesos que no suman 1
En este ejemplo, los pesos están configurados para sumar 1, por lo que el divisor siempre es 1 y el resultado es el valor devuelto por SUMPRODUCT. Sin embargo, una característica interesante de la fórmula es que no es necesario que los pesos sumen 1.
Por ejemplo, podríamos usar una ponderación de 1 para las dos primeras pruebas y una ponderación de 2 para la final (ya que la final es dos veces más importante) y la media ponderada será la misma:
En la celda G5, la fórmula se resuelve así:
=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: los valores entre llaves () anteriores son rangos expresados como matrices.
Transposición de pesos
La función SUMPRODUCTO requiere que las dimensiones de la matriz sean compatibles. Si las dimensiones no son compatibles, SUMPRODUCT devolverá un error #VALUE. En el siguiente ejemplo, los pesos son los mismos que en el ejemplo original, pero se enumeran en un rango vertical:
Para calcular un promedio ponderado con la misma fórmula, necesitamos "voltear" los pesos en una matriz horizontal con la función TRANSPONER como esta:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
Después de ejecutar TRANSPOSE, la matriz vertical:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
se convierte en:
=(0.25,0.25,0.5) // horizontal array
Y a partir de este punto, la fórmula se comporta como antes.
Leer más: matrices verticales y horizontales.
