Excel ha hecho que calcular el promedio de múltiples celdas sea una tarea muy fácil: solo use la función PROMEDIO (PROMEDIO). Pero, ¿y si unos valores pesan más que otros? Por ejemplo, en muchos cursos, las pruebas tienen más peso que las tareas. Para tales casos, es necesario calcular peso promedio.
Excel no tiene una función para calcular el promedio ponderado, pero hay una función que hará la mayor parte del trabajo por usted: SUMPRODUCT (SUMA PRODUCTO). E incluso si nunca antes ha usado esta función, al final de este artículo la estará usando como un profesional. El método que utilizamos funciona en cualquier versión de Excel, así como en otras hojas de cálculo como Google Sheets.
Preparamos la mesa
Si va a calcular un promedio ponderado, necesitará al menos dos columnas. La primera columna (columna B en nuestro ejemplo) contiene los puntajes de cada tarea o prueba. La segunda columna (columna C) contiene los pesos. Más peso significa más influencia de la tarea o prueba en la calificación final.
Para entender qué es el peso, puedes considerarlo como un porcentaje de tu nota final. De hecho, este no es el caso, ya que en este caso los pesos deberían sumar 100%. La fórmula que analizaremos en esta lección calculará todo correctamente y no depende de la cantidad que sumen los pesos.
Introducimos la fórmula
Ahora que nuestra tabla está lista, agregamos la fórmula a la celda B10 (cualquier celda vacía servirá). Como con cualquier otra fórmula en Excel, comenzamos con un signo igual (=).
La primera parte de nuestra fórmula es la función SUMPRODUCT (SUMA PRODUCTO). Los argumentos deben ir entre paréntesis, así que los abrimos:
=СУММПРОИЗВ(
=SUMPRODUCT(
A continuación, agregue los argumentos de la función. SUMPRODUCT (SUMPRODUCT) puede tener varios argumentos, pero normalmente se utilizan dos. En nuestro ejemplo, el primer argumento será un rango de celdas. B2: B9que contiene las puntuaciones.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
El segundo argumento será un rango de celdas. C2:C9, que contiene los pesos. Estos argumentos deben estar separados por un punto y coma (coma). Cuando todo esté listo, cierre los corchetes:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Ahora agreguemos la segunda parte de nuestra fórmula, que dividirá el resultado calculado por la función SUMPRODUCT (SUMAPRODUCTO) por la suma de los pesos. Discutiremos más adelante por qué esto es importante.
Para realizar la operación de división, continuamos la fórmula ya ingresada con el símbolo / (barra inclinada), y luego escriba la función SUM (SUMA):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
para la función SUM (SUMA) especificaremos solo un argumento: un rango de celdas C2:C9. No olvide cerrar los paréntesis después de ingresar el argumento:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
¡Listo! Después de presionar la tecla Participar, Excel calculará el promedio ponderado. En nuestro ejemplo, el resultado final será 83,6.
Cómo funciona
Analicemos cada parte de la fórmula, comenzando con la función SUMPRODUCT (SUMPRODUCT) para entender cómo funciona. Función SUMPRODUCT (SUMPRODUCT) calcula el producto de la puntuación de cada elemento y su peso, y luego suma todos los productos resultantes. En otras palabras, la función encuentra la suma de los productos, de ahí el nombre. Entonces para Tareas 1 multiplica 85 por 5, y para La prueba multiplica 83 por 25.
Si te preguntas por qué necesitamos multiplicar los valores en la primera parte, imagina que cuanto mayor sea el peso de la tarea, más veces tendremos que considerar la nota de la misma. Por ejemplo, Tarea 2 contado 5 veces y Examen final – 45 veces. Es por eso Examen final tiene un mayor impacto en la nota final.
A modo de comparación, al calcular la media aritmética habitual, cada valor se tiene en cuenta solo una vez, es decir, todos los valores tienen el mismo peso.
Si pudieras mirar debajo del capó de una función SUMPRODUCT (SUMPRODUCT), vimos que de hecho ella cree esto:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
Afortunadamente, no necesitamos escribir una fórmula tan larga porque SUMPRODUCT (SUMPRODUCT) hace todo esto automáticamente.
Una función en sí misma SUMPRODUCT (SUMAPRODUCTO) nos devuelve un número enorme: 10450. En este punto, entra en juego la segunda parte de la fórmula: /SUMA(C2:C9) or /SUMA(C2:C9), que devuelve el resultado al rango normal de puntajes, dando la respuesta 83,6.
La segunda parte de la fórmula es muy importante porque te permite corregir automáticamente los cálculos. ¿Recuerdas que los pesos no tienen que sumar 100%? Todo esto gracias a la segunda parte de la fórmula. Por ejemplo, si aumentamos uno o más valores de peso, la segunda parte de la fórmula simplemente se dividirá por el valor más grande, lo que nuevamente dará como resultado la respuesta correcta. O podemos hacer que los pesos sean mucho más pequeños, por ejemplo, especificando valores como 0,5, 2,5, 3 or 4,5y la fórmula seguirá funcionando correctamente. Es genial, ¿verdad?