Excel

Peso promedio

Weighted Average

Fórmula de Excel: promedio ponderadoFórmula genérica
= SUMPRODUCT (weights,values)/ SUM (weights)
Resumen

Para calcular un promedio ponderado, puede utilizar el SUMPRODUCTO funcionar junto con el Función SUM . En el ejemplo que se muestra, la fórmula en G5, copiada, es:

|_+_|

dónde pesos es el rango con nombre 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 a 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 celdas:



funciones de ms excel con ejemplo
|_+_|

En la hoja de trabajo que se muestra, los puntajes de 3 pruebas aparecen en las columnas C a E, y los pesos están en el rango con nombre pesos (I5: K5). La fórmula en la celda G5 es:

|_+_|

Trabajando de adentro hacia afuera, primero usamos la función SUMPRODUCTO para multiplicar los pesos por las puntuaciones correspondientes y sumar el resultado:



|_+_|

SUMPRODUCT primero multiplica los elementos correspondientes de las dos matrices juntas, luego devuelve la suma del producto:

|_+_|

Luego, el resultado se divide por la suma de los pesos:

|_+_|

A medida que la fórmula se copia en la columna G, el rango con nombre pesos I5: K5 no cambia, ya que se comporta como un referencia absoluta . Sin embargo, los puntajes en C5: E5, ingresados ​​como referencia relativa , actualice en cada nueva fila. 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 el Función PROMEDIO :



|_+_|

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 el doble de importante) y la media ponderada será la misma:

Promedio ponderado de Excel con pesos personalizados

agregar un encabezado que contenga el nombre de la hoja

En la celda G5, la fórmula se resuelve así:

|_+_|

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 vertical abarcar:

Promedio ponderado de Excel con TRANSPOSE

Para calcular un promedio ponderado con la misma fórmula, necesitamos 'voltear' los pesos en una matriz horizontal con el Función TRANSPONER como esto:

|_+_|

Después de ejecutar TRANSPOSE, la matriz vertical:

|_+_|

se convierte en:

|_+_|

Y a partir de este punto, la fórmula se comporta como antes.

Leer más: matrices verticales y horizontales .

Archivos adjuntos Archivo promedio ponderado de exceljet.xlsx Autor Dave Bruns


^