A diferencia del promedio normal, el promedio ponderado toma en cuenta el "peso" relativo de cada uno de los datos en relación al resto de los datos.
Por ejemplo, en este caso
El precio promedio, sin tomar en cuenta las cantidades vendidas por cada sucursal, es 15.50. Pero si tomamos en cuenta el "peso" de cada sucursal, vemos que en la mayor parte de las ventas el precio está por debajo del promedio, por lo cual el promedio ponderado noos da 12.84.
El promedio ponderado lo calculamos con la fórmula
=SUMAPRODUCTO(C3:C6,D3:D6)/SUMA(C3:C6)
Alternativamente podríamos agregar totales a la tabla y calcular el promedio ponderado dividiendo el total de venta (cantidad x precio) por el total de piezas vendidas
Ahora vamos a complicar las cosas y ver cómo podemos calcular el promedio ponderado en tablas filtradas (tablas en las que hemos aplicado Autofiltro u ocultado filas).
Veamos este ejemplo
El promedio sencillo del precio lo podemos calcular con SUBTOTALES, que ignora las celdas ocultas
Para calcular el promedio ponderado se nos presenta el problema el problema que, a diferencia de la función SUBTOTALES, SUMPRODUCTO toma en cuenta también las celdas ocultas en el rango.
Una forma de calcular el promedio ponderado en listas filtradas es usar columnas auxiliares (en este caso una única columna auxiliar), usando la función SUBTOTALES con la opción 103 (CONTARA)
Al referirse a una única celda la función dará un valor 0 si la celda está en una fila oculta y 1 si la fila es visible. Luego podemos usar esta columna para calcular el promedio ponderado usando SUMPRODUCTO
=SUMAPRODUCTO(D4:D15*E4:E15*F4:F15)/SUMAPRODUCTO(D4:D15*F4:F15)
Si queremos evitar el uso de columnas auxiliares podemos usar esta fórmula que combina SUMAPRODUCTO con SUBTOTALES y DESREF
=SUMAPRODUCTO(SUBTOTALES(3,DESREF(B4:B15,FILA(B4:B15)-FILA(B4),,1)),D4:D15,E4:E15)/SUBTOTALES(9,D4:D15)
La expresión
SUBTOTALES(3,DESREF(B4:B15,FILA(B4:B15)-FILA(B4),,1))
da un vector de 1 (fila visible) o 0 (fila oculta). Para comprobarlo podemos seleccionar la expresión en la barra de las fórmulas y apretar F9 para ver el resultado
Esta fórmula de autoría incierta (posiblemente Bob Philips), aparece en varios foros de Excel.
Que fuerte Jorge. Esto son matemáticas puras no?
ResponderBorrarMás bien diría que es una prueba más de que siempre se puede encontrar la forma de hacer cálculos con Excel.
ResponderBorrarJorge,
ResponderBorrarAnte todo felicitarte y agradecerte tanta divulgación de conocimiento y socialización del saber. Dicho esto queria consultarte si pudieras orientarme como poder establecer un "predictivo" de la cantidad de efectivo que pudiera dispensar un Cajero Automatico en los distintos dias de la semana y del mes, previendo la estacionalidad del mes y del año. Los datos con los que cuento son variados, pero considero que en relación a un historico de los días, cantidad de transaccion y dinero dispensado entiendo que lo debería poder establecer.
Quedo al aguardo de tu ayuda.
Desde ya muchisimas gracias.
Carlos, si cuentas con los datos diarios, puedes calcular promedios para días y meses del año. Pero, ¿cómo puedo ayudarte?
ResponderBorrarJorge, buenas tardes muchas gracias por compartir tus conocimientos y mantener hasta el dia de hoy este gran e interesante blog.
ResponderBorrarHe estado realizando algunos ejercicios con SUMAPRODUCTO y sin resultados mi problema es el siguiente, yo trabajo en un Call Center y lo que tengo que sacar es el Tiempo Promedio PONDERADO del Ejecutivo en forma mensual en la HOJA 1 tengo los datos :
LOGINID I Nombre Ejecutivo I Servicio I LLamadas I TMO
85670 Juanito mau 20 0:02:30
85670 Juanito saet 8 0:03:45
86489 Maria saet 5 0:01:55
Esta información va a variar mes a mes , va a depender de cuantos servicios se les asigno al ejecutivo, ahora he tratado que en la Hoja B, me traiga el TMO ponderado con condiciones , es decir mi clave primaria es el LOGINID pero no logro combinar las condiciones en la funcion, la idea es que me tome todos los registros que tuvo ej ejecutivo en el mes y me de ponderado el promedio.
HOJA B
LOGINID NOMBRE TMO PONDERADO
85670 Juanito """ ERROR""""
86489 Maria
realmente no se que es lo que no me funciona....
=SUMAPRODUCTO('DATOS HOJA A'!R2:R20;'DATOS HOJA A'!M2:M20)/SUMA('DATOS HOJA A'!M2:M20)
gracias
Francisco, por favor fijaate en lo que pongo en el enlace Ayuda (en la parte superior de la plantilla) y ponte en contacto conmigo por mail privado.
ResponderBorrarQuiero comentar un asunto que tiene algo...de relación con este post.
ResponderBorrarAplico fórmula de promedios en una celda y me aplica el redondeo; así:
6.3 aparece en pantalla; si yo doy clic en la celda me despliega 6.2666667. Yo necesito que el valor quede como 6.3 porque en otra celda se efectúa un cálculo con el valor de redondeo y no con el valor exacto. El tipo de dato debe ser numérico. ¿Es posible esto? Muchas gracias
SI, anidando PROMEDIO en REDONDEAR. Por ejemplo, =REDONDEAR(PROMEDIO(rango de valores),1)
ResponderBorrarbuen aporte, muchas gracias
ResponderBorrar