Calcular el promedio ponderado en Excel
Cuando calculamos un promedio simple el supuesto detrás es que cada valor tiene el mismo peso o importancia en el cálculo.
¿Qué pasa si uno de los valores pesa más que otro en el cálculo y por ende contribuye más en el promedio? En esos casos debemos realizar un promedio ponderado.
En este video-tutorial te enseño la fórmula para obtener el promedio o media ponderada con la ayuda de las funciones SUMAPRODUCTO y SUMA.
Explicación
Si quisieramos obtener el promedio de sueldos que paga la empresa, aplicar un promedio o media simple sería incorrecto.
Como verán en la imagen de abajo, cada área tiene una cantidad diferente de empleados: así por ejemplo RRHH tiene mayor cantidad de empleados que, por ejemplo la Gerencia, por lo cual al momento de calcular el promedio RRHH debería tener un peso mayor dentro del cálculo que la Gerencia.
Para calcular la media ponderado no hay una función de Excel predeterminada, pero con la combinación de funciones SUMAPRODUCTO y SUMA podemos obtenerlo.
Formula General
=SUMAPRODUCTO(números,ponderador)/SUMA(ponderador)
En nuestro ejemplo la fórmula sería:
=SUMAPRODUCTO(B4:B8;C4:C8)/SUMA(C4:C8)
La función SUMAPRODUCTO es como dos funciones en una ya que realiza la multiplicación (o el producto) de los valores pertenecientes a las matrices proporcionadas y luego hace la suma de todos esos productos. Así por ejemplo multiplica 24.000 x 10; 12.000 x 5; 34.000 x 12 ,y así sucesivamente y luego suma esos resultados.
Ese resultado de la función SUMAPRODUCTO es dividido por la suma de los ponderadores y así es como se obtiene el promedio o media ponderada.
Caso: Los ponderadores suman 1.
Hay casos en que los ponderadores están expresados de tal manera que su suma es equivalente a 1. Un ejemplo típico es el caso de las calificaciones de alumnos que tienen diferentes tareas con diferente peso en la calificación final.
En este caso, como el peso de la calificación está expresado como un % del total, la suma me da 100% o 1, por lo cual en casos así no es necesario dividir la función SUMAPRODUCTO.
=SUMAPRODUCTO(B4:B10;C4:C10)
Interesante ¿no?
¿Tienen alguna otra forma de calcular el promedio ponderado? Cuentenme en los comentarios de abajo.
¡Saludos!
Cecilia - www.planillaexcel.com
PD: si quieres aprender más de Excel, dicto cursos online desde el nivel básico a avanzado.
Haz click aquí para ver los cursos.