Promedio ponderado en Excel

En Excel es muy sencillo determinar el promedio ponderado de una serie de valores sólo utilizando un par de fórmulas y sin necesidad de entender el concepto de promedio ponderado, que a suele ser la parte difícil.

Qué es el promedio ponderado.

Lo primero que debemos hacer es entender el concepto de promedio ponderado, que se diferencia al promedio simple en que en el promedio ponderado se considera el peso o importancia que tiene un valor respecto a otro, o respecto al total.

El ejemplo típico de un promedio ponderado son las notas parciales de la universidad. La primera nota parcial puede tener un valor del 30%, la segunda nota parcial un valor del 30% y la tercera nota parcial un 40%.

En este caso, un 4 en la primera nota parcial pesa mucho menos que un 4 en la tercera nota parcial. Un 0 del primer parcial puede ser recuperado por un 5 del parcial final.

Supongamos que se requiere determinar cuál es el salario promedio de un empleado de una empresa con la siguiente información:

Hay 50 empleados y hay 6 rangos de sueldos. Un promedio simple sería tomar la suma de todos los salarios y dividirlos entre 6 y el resultado sería:

19.000.000/6 =  3.166.667.

Afirmar que cada empleado de dicha empresa obtiene un salario promedio de más de 3.000.000 es incorrecto, puesto que la gran mayoría apenas gana 700.000. Una gran forma de falsear la realidad.

Quien vea una información así creerá que es la mejor empresa para trabajar, pero lo cierto es que sólo es buena para los jefes que ganan mucho, más no para los operarios que apenas ganar para sobrevivir.

Fórmula del promedio ponderado en Excel.

No existe una función que por sí sola determina el promedio ponderado en Excel, así que es necesario recurrir a una combinación de funciones.

Para ello en Excel utilizamos la función SUMAPRODUCTO junto con la función SUMA.

Suponiendo que el valor de los salarios esté en el rango A1:A7 y el número de empleados con cada sueldo en el rango B2:B7, la fórmula a utilizar será:

=SUMAPRODUCTO(A2:A7*B2:B7)/SUMA(B2:B7)

Esta fórmula nos arroja un promedio ponderado de 1.210.000, un valor mucho más realista que 3.166.667.

Ejemplo de promedio ponderado en Excel.

Hemos desarrollado un ejemplo elemental sobre cómo determinar el promedio ponderado en Excel.

Descargar ejemplo del promedio ponderado en Excel.

Lo que hace la fórmula utilizada es tomar los sueldos y multiplicarlos por el número de empleados con cada sueldo y sumar el resultado, para luego dividir dicho resultado por el número total de empleados.

Si lo que buscaba era el método del promedio ponderado en el control de inventarios, aquí puede consultarlo.

Ponderación de notas en Excel.

En el caso de las notas de los estudiantes, donde cada nota tiene un peso distinto para al final consolidar una nota final, se puede utilizar la función SUMAPRODUCTO sin ningún valor adicional, o hacer de forma más manual multiplicando el peso de la nota por la nota obtenida por el estudiante.

Supongamos la siguiente tabla de notas:

La nota ponderada se puede se puede obtener multiplicando el valor de cada nota por su peso.

En el caso de la primera nota que vale el 30%, y el estudiante obtiene una nota de cuatro, su nota ponderada es de 1.2 que resulta de multiplicar 4*30%.

Así se hace con cada nota y luego se suman las notas ponderadas para determinar la nota final.

La nota final se puede determinar de forma global utilizando la siguiente fórmula:

=SUMAPRODUCTO(B2:B5;C2:C5)

En la columna B está el peso de cada nota, y en la columna C está la nota obtenida en cada examen o evaluación.

De esta forma no se hace necesario sacar la ponderación de cada una de las 4 notas, sino que se llega a la nota final de una sola vez.

Descargue ejemplo en Excel.

En este archivo propusimos un ejemplo de varios estudiantes en una tabla, donde se puede determinar la nota final ponderada con la función SUMAPRODUCTO, pero debido a la distribución de la tabla requiere una modificación que pasamos a explicar.

No es posible utilizar la función SUMAPRODUCTO porque el rango que contiene los porcentajes está en las filas y el que contiene las notas está en columnas.

Para solucionar el problema se debe transponer los valores en columnas para colocarlos en filas, quedando la fórmula de la siguiente forma:

{=SUMAPRODUCTO(A2:A5;TRANSPONER(C2:F2))}

Como se puede observar, se trata de una fórmula matricial en razón a que está encerrada en corchetes para lo cual se debe presionar simultáneamente las teclas Shift + Ctrl +Enter.

Promedio simple en Excel.

El promedio ponderado simple no es más que la media aritmética de los valores de un conjunto.

Por ejemplo, si tenemos los valores 5, 10 y 15, su media aritmética es de 10, calculada así:

5 + 10 + 15 /3 = 10

Se suman los valores y el resultado se divide por la cantidad de valores sumados.

En Excel se hace con la función promedio, así:

=PROMEDIO(A1:A10)

Con ello Excel toma los valores que haya en ese rango y lo divide por el número de celdas de dicho rango, que en este caso son 10.

Definir nombres de rango en Excel

Cálculo de la depreciación con Excel