Cuando calculamos un promedio simple de un conjunto de valores dado, se supone que todos los valores tienen el mismo peso o importancia.
Por ejemplo, si se presenta a los exámenes y todos los exámenes tienen un peso similar, entonces el promedio de sus calificaciones totales también sería el promedio ponderado de sus calificaciones.
Sin embargo, en la vida real, este difícilmente es el caso.
Algunas tareas son siempre más importantes que otras. Algunos exámenes son más importantes que otros.
Y ahí es donde Peso promedio entra en escena.
Aquí está la definición de libro de texto de promedio ponderado:
Ahora veamos cómo calcular el promedio ponderado en Excel.
Calcular el promedio ponderado en Excel
En este tutorial, aprenderá a calcular el promedio ponderado en Excel:
- Utilizando la función SUMPRODUCTO.
- Usando la función SUM.
Entonces empecemos.
Cálculo del promedio ponderado en Excel - Función SUMPRODUCT
Puede haber varios escenarios en los que necesite calcular el promedio ponderado. A continuación se muestran tres situaciones diferentes en las que puede utilizar la función SUMPRODUCT para calcular el promedio ponderado en Excel
A continuación se muestran tres situaciones diferentes en las que puede utilizar la función SUMPRODUCT para calcular el promedio ponderado en Excel
Ejemplo 1: cuando los pesos suman 100%
Suponga que tiene un conjunto de datos con las calificaciones obtenidas por un estudiante en diferentes exámenes junto con las ponderaciones en porcentajes (como se muestra a continuación):
En los datos anteriores, un estudiante obtiene calificaciones en diferentes evaluaciones, pero al final, debe recibir una calificación o calificación final. Aquí no se puede calcular un promedio simple ya que varía la importancia de las diferentes evaluaciones.
Por ejemplo, un cuestionario con un peso del 10% tiene el doble de peso en comparación con una tarea, pero una cuarta parte del peso en comparación con el examen.
En tal caso, puede usar la función SUMPRODUCTO para obtener el promedio ponderado de la puntuación.
Aquí está la fórmula que le dará el promedio ponderado en Excel:
= SUMPRODUCTO (B2: B8, C2: C8)
Así es como funciona esta fórmula: La función SUMPRODUCTO de Excel multiplica el primer elemento de la primera matriz con el primer elemento de la segunda matriz. Luego multiplica el segundo elemento de la primera matriz con el segundo elemento de la segunda matriz. Etcétera…
Y finalmente, suma todos estos valores.
Aquí hay una ilustración para aclararlo.
Ejemplo 2: cuando los pesos no suman el 100%
En el caso anterior, las ponderaciones se asignaron de tal manera que el total sumara el 100%. Pero en escenarios de la vida real, puede que no siempre sea así.
Echemos un vistazo al mismo ejemplo con diferentes pesos.
En el caso anterior, los pesos suman 200%.
Si utilizo la misma fórmula SUMPRODUCT, me dará un resultado incorrecto.
En el resultado anterior, dupliqué todos los pesos y devuelve el valor promedio ponderado como 153.2. Ahora sabemos que un estudiante no puede obtener más de 100 sobre 100, sin importar lo brillante que sea.
La razón de esto es que los pesos no suman el 100%.
Aquí está la fórmula que ordenará esto:
= SUMPRODUCTO (B2: B8, C2: C8) / SUM (C2: C8)
En la fórmula anterior, el resultado SUMPRODUCTO se divide por la suma de todos los pesos. Por lo tanto, pase lo que pase, los pesos siempre sumarían el 100%.
Ejemplo 3: cuando es necesario calcular los pesos
En el ejemplo cubierto hasta ahora, se especificaron los pesos. Sin embargo, puede haber casos en los que las ponderaciones no estén disponibles directamente y deba calcular las ponderaciones primero y luego calcular el promedio ponderado.
Suponga que vende tres tipos diferentes de productos como se menciona a continuación:
Puede calcular el precio medio ponderado por producto mediante la función SUMPRODUCTO. Aquí está la fórmula que puede usar:
= SUMPRODUCTO (B2: B4, C2: C4) / SUM (B2: B4)
Dividir el resultado SUMPRODUCT con la SUMA de cantidades asegura que los pesos (en este caso cantidades) sumen 100%.
Cálculo del promedio ponderado en Excel - Función SUMA
Si bien la función SUMPRODUCTO es la mejor manera de calcular el promedio ponderado en Excel, también puede usar la función SUM.
Para calcular el promedio ponderado con la función SUMA, debe multiplicar cada elemento, con su importancia asignada en porcentaje.
Usando el mismo conjunto de datos:
Aquí la fórmula que le dará el resultado correcto:
= SUMA (B2 * C2, B3 * C3, B4 * C4, B5 * C5, B6 * C6, B7 * C7, B8 * C8)
Este método está bien para usar cuando tienes un par de artículos. Pero cuando tiene muchos artículos y pesos, este método puede resultar engorroso y propenso a errores. Hay una manera mejor y más corta de hacer esto usando la función SUM.
Continuando con el mismo conjunto de datos, aquí está la fórmula corta que le dará el promedio ponderado usando la función SUMA:
= SUMA (B2: B8 * C2: C8)
El truco al usar esta fórmula es usar Control + Shift + Enter, en lugar de simplemente usar Enter. Dado que la función SUM no puede manejar matrices, debe usar Control + Shift + Enter.
Cuando presiona Control + Shift + Enter, verá aparecer corchetes automáticamente al principio y al final de la fórmula (vea la barra de fórmulas en la imagen de arriba).
Nuevamente, asegúrese de que los pesos sumen el 100%. Si no es así, debe dividir el resultado por la suma de los pesos (como se muestra a continuación, tomando el ejemplo del producto):
