01 / 14Nueve Delegaciones y un Solo Titular

PromediosPromedios PonderadosInformes

Promedio de promedios en Excel: por qué tu porcentaje falla

El Consejo Aprobó un 91,1% de Puntualidad y el Cliente Facturó 38.000 € en Penalizaciones, Porque el Panel Promedió los Promedios de Cada Delegación

Marchwood Care gestiona nueve delegaciones contra un solo número: el porcentaje de visitas que empiezan a su hora. El contrato cobra una penalización por cada punto por debajo del 90%. El panel marcaba 91,1% y la auditoría del cliente, 86,5%.

En la hoja no fallaba nada. La columna D dividía visitas puntuales entre visitas, delegación por delegación, y cada porcentaje era correcto. La celda del titular promediaba esa columna.

PROMEDIO no sabe cuántas visitas hay detrás de cada porcentaje. Suma ocho números y divide entre ocho, así que una delegación de 48 visitas pesa lo mismo que una de 1.420.

MedidaEn el panelEn el contrato
Puntualidad del mes91,1%86,5%
Fórmula=PROMEDIO(D2:D10)=SUMA(C2:C10)/SUMA(B2:B10)
Puntos por debajo del 90%ninguno3,5
Penalización del mes0 €14.000 €
Penalización del trimestre0 €38.000 €

Alcance. PROMEDIO, PROMEDIOA, PROMEDIO.SI y PROMEDIO.SI.CONJUNTO en Excel para Microsoft 365, 2021, 2019 y Excel para Mac. PROMEDIO.SI.CONJUNTO necesita Excel 2007 o posterior.

La ponderación se comporta igual en Google Sheets. En toda la guía, la columna B son visitas, la C visitas puntuales y la D el porcentaje de cada delegación, de la fila 2 a la 10.

01Nueve Delegaciones y un Solo Titular

El mes son nueve filas. Visitas en B, visitas puntuales en C y un porcentaje en D que dice =SI.ERROR(C2/B2;"").

Todos los porcentajes de la columna D son correctos. Ashbourne empezó de verdad 1.207 de sus 1.420 visitas a su hora, y Brindle Park empezó de verdad las 96 suyas.

Tres de las nueve delegaciones son tan pequeñas que desaparecen. Brindle Park abrió en mayo e hizo 96 visitas, Dunhollow cubre un solo pueblo e hizo 48, Fallowfield hizo 74. Entre las tres, 218 visitas de 6.498.

Hestham abrió el día 29 y no hizo ninguna, así que SI.ERROR dejó texto vacío en su celda de porcentaje. La sección 6 trata de lo que esa única celda le hace a un mes.

Nueve Delegaciones, Ocho Votos Iguales

El informe mensual de servicio de Marchwood Care, en A2:F14. La columna B son visitas, la C visitas puntuales y la D es `=SI.ERROR(C2/B2;"")`, el porcentaje de cada delegación, correcto hasta el decimal. Brindle Park, Dunhollow y Fallowfield hicieron 218 visitas entre las tres y tienen los tres porcentajes más altos; Ashbourne, Cawthorne, Eastgate, Garrowby e Ivelet hicieron 6.280 y ninguna pasó del 88%. Hestham abrió el día 29 y tiene texto vacío donde iría su porcentaje, así que PROMEDIO divide entre ocho y no entre nueve. Las tres últimas filas son el mismo mes leído de tres maneras: la media de la columna del panel, el cociente de totales del contrato y lo que cuesta la diferencia.

ABCDEF
1
Branch
Visits
On time
On-time %
Share of visits
What happened
2
Ashbourne
1420
1207
85.0%
21.9%
The biggest round in the group, and the branch the headline should mostly be about. It did 21.9% of the month's visits and carries 12.5% of the dashboard figure.
3
Brindle Park
96
96
100.0%
1.5%
Opened in May, two visits a day, and every one of them on time. A real 100%, and worth exactly as much to =AVERAGE(D2:D10) as Ashbourne's 1,420 visits.
4
Cawthorne
1180
1003
85.0%
18.2%
The second-biggest round and the second branch at exactly 85%. Three of the four large branches sit between 85% and 88%, which is what the month actually looked like.
5
Dunhollow
48
48
100.0%
0.7%
One village, one carer, 48 visits. Seven tenths of one per cent of the work, and an eighth of the reported month.
6
Eastgate
1400
1218
87.0%
21.5%
The best of the large rounds at 87%, and still four points below what the dashboard claimed for the group as a whole.
7
Fallowfield
74
73
98.6%
1.1%
One late visit in the whole month out of 74. On a round this size a single van in traffic is the difference between 100% and 98.6%.
8
Garrowby
1300
1144
88.0%
20.0%
The strongest large branch at 88%. Even this one, the best of the four that do the work, is below the 90% the contract asks for.
9
Hestham
0
0
0.0%
Opened on the 29th and did no visits at all, so IFERROR left empty text in D9. AVERAGE skips it, AVERAGEA counts it as nought, and SUMPRODUCT refuses the column outright with #VALUE!.
10
Ivelet
980
833
85.0%
15.1%
The fourth large round, at 85% like the other two. Four branches did 5,900 of the month's 6,498 visits and none of them cleared 88%.
11
Dashboard: AVERAGE of D
91.1%
—
Eight percentages added and divided by eight. The three smallest branches are three of those eight numbers, which is why a month that ran at 86.5% was reported as a pass.
12
Contract: on time / visits
6498
5622
86.5%
100.0%
5,622 visits of 6,498 started on time. This is the figure the contract defines, it uses no percentage column at all, and it is 4.6 points below the one that went to the board.
13
Service credit at 90%
3.5 points
£14,000
The contract charges £4,000 for every point below 90%, measured across the contract. This month cost £14,000 and the quarter cost £38,000, all of it on months the dashboard passed.

fxLas celdas con fórmulas están resaltadas en verde

Pasa el mouse sobre las celdas con fórmulas para ver la fórmula y resaltar las celdas referenciadas

02PROMEDIO Pondera Filas, no Visitas

=PROMEDIO(D2:D10) devuelve 91,1% y hace exactamente lo que promete. Suma los números que ve y divide entre cuántos había: ocho, porque la novena celda contiene texto.

Así que cada delegación de la columna D vale el 12,5% del titular, haya hecho lo que haya hecho. Las 48 visitas de Dunhollow pesan lo mismo que las 1.400 de Eastgate.

DelegaciónVisitas% puntualPeso en =PROMEDIO(D2:D10)Peso en las visitas
Ashbourne1.42085,0%12,5%21,9%
Eastgate1.40087,0%12,5%21,5%
Brindle Park96100,0%12,5%1,5%
Dunhollow48100,0%12,5%0,7%
Hestham0—ninguno0,0%

Las dos columnas de la derecha son todo el artículo. Una media sobre filas pondera filas; el contrato pondera visitas.

Escenario: En tu propia hoja, pon =PROMEDIO(D2:D10) y =SUMA(C2:C10)/SUMA(B2:B10) en dos celdas contiguas y resta una de otra. Cualquier cosa distinta de cero significa que tu titular lo pondera el número de filas.

Pruébalo en la cuadrícula

03Las Tres Delegaciones Más Pequeñas Pesaban un 37,5%

Brindle Park, Dunhollow y Fallowfield hicieron 218 visitas entre las tres, el 3,4% del mes. En =PROMEDIO(D2:D10) son tres de ocho números, así que se llevan el 37,5% de la respuesta.

Además son los tres porcentajes más altos de la hoja, y no es suerte. =CONTAR.SI.CONJUNTO(B2:B10;"<100";D2:D10;">0,95") devuelve 3: toda delegación por debajo de cien visitas pasó del 95%.

Una ruta de dos visitas al día puede cumplir las dos. Una de cincuenta no puede tener un día limpio, porque una furgoneta en un atasco ya es una visita tardía.

Borra esas tres filas y la misma fórmula equivocada devuelve 86,0%, a medio punto de la verdad. La fórmula no mejoró: las filas se parecieron más entre sí.

Escenario: Ordena tu tabla por volumen con =ORDENAR(A2:F10;2;-1), promedia los cinco porcentajes de arriba y luego los cinco de abajo. Más de un punto de diferencia significa que el titular habla de tus delegaciones pequeñas.

Pruébalo en la cuadrícula

04Divide los Totales, no Promedies los Cocientes

El contrato define la puntualidad como visitas puntuales entre visitas, para todo el contrato. Esa frase ya es una fórmula: =SUMA(C2:C10)/SUMA(B2:B10), que devuelve 86,5%.

No usa la columna D para nada. La columna de porcentajes existe para que un responsable lea una delegación; el titular sale de las dos columnas de recuentos que tiene al lado.

Un cociente de totales no es una media de cocientes, y solo uno de los dos es el número que prometiste. 5.622 visitas de 6.498 empezaron a su hora, y 86,5% es la única aritmética que lo dice.

Si prefieres poner nombre a las partes, =LET(v;SUMA(B2:B10);p;SUMA(C2:C10);p/v) devuelve la misma cifra y explica lo que hace.

Escenario: Pon =SUMA(C2:C10)/SUMA(B2:B10) justo encima de la cifra de tu panel, con un decimal. Deja las dos en la hoja un mes y mira cuál cita la siguiente reunión.

Pruébalo en la cuadrícula

05Cuando Solo Tienes los Porcentajes

A veces los recuentos ya no están. Un proveedor manda porcentajes, o el archivo del año pasado guardó la columna D y nada más. Entonces pondera tú, con los volúmenes que sí tengas.

=SUMAPRODUCTO(B2:B10;D2:D10)/SUMA(B2:B10) devuelve el mismo 86,5%. Multiplica cada porcentaje por sus visitas, suma los productos y divide entre el total de visitas.

El numerador merece su propio control. =SUMAPRODUCTO(B2:B10;D2:D10) devuelve 5.622, que es justo =SUMA(C2:C10). Si esos dos no coinciden, algún porcentaje se escribió a mano en vez de calcularse.

Una celda lo rompe. SUMAPRODUCTO devuelve #¡VALOR! en cuanto D9 contiene texto vacío en vez de no contener nada.

Deja esa celda realmente vacía o protege la columna: =SUMAPRODUCTO(B2:B10;SI(ESNUMERO(D2:D10);D2:D10;0))/SUMA(B2:B10). Una delegación sin visitas no pesa nada de todos modos.

06Vacío, Cero y Texto Vacío Son Tres Cosas Distintas

PROMEDIO se salta las celdas vacías y se salta el texto. No se salta el cero. Esas dos frases deciden tu titular, y nada en la cara de la hoja te dice en cuál de los casos estás.

Hestham no hizo ninguna visita en el mes. Según lo que haya en D9, el mismo =PROMEDIO(D2:D10) cuenta ese mes de tres maneras.

Qué hay en D9=PROMEDIO(D2:D10)=CONTAR(D2:D10)
Nada en absoluto91,1%8
=SI.ERROR(C9/B9;"")91,1%8
El texto n/d91,1%8
0, escrito por si acaso81,0%9

Diez puntos de servicio declarado dependen de si alguien rellenó un hueco con un cero. Una delegación que no hizo nada no falló: no tiene porcentaje, y un cero afirma que sí lo tiene.

Escenario: Pon =CONTAR(D2:D10), =CONTARA(D2:D10) y =FILAS(D2:D10) en tres celdas. Aquí dan 8, 9 y 9: una celda guarda algo que PROMEDIO no cuenta. Cualquier diferencia entre las dos primeras es texto escondido.

Pruébalo en la cuadrícula

07PROMEDIOA No Es un PROMEDIO Más Seguro

PROMEDIOA parece la versión concienzuda de la misma función. Es lo contrario: cuenta las celdas que PROMEDIO hacía bien en saltarse, y las cuenta como cero.

=PROMEDIOA(D2:D10) devuelve 81,0% en esta hoja. El texto vacío de D9 se convierte en un cero, el divisor pasa a nueve y una delegación que no trabajó hunde al grupo cinco puntos.

Los valores lógicos son peores. PROMEDIOA lee VERDADERO como 1, y un 1 en una columna de porcentajes es un 100%, así que una casilla marcada inventa una delegación perfecta.

Contenido de la celdaPROMEDIOPROMEDIOA
Celda vacíalo ignoralo ignora
Texto vacío de SI.ERRORlo ignoracuenta 0
Cualquier otro textolo ignoracuenta 0
VERDADEROlo ignoracuenta 1
0cuenta 0cuenta 0

El texto con pinta de porcentaje es un tercer caso. =VALOR(D9) convierte el texto 85,0% en 0,85, y ese es el arreglo; hasta que lo ejecutas, PROMEDIO promedia menos filas de las que enseña la hoja.

08PROMEDIO.SI y PROMEDIO.SI.CONJUNTO También Ponderan Filas

Filtrar no es ponderar. =PROMEDIO.SI.CONJUNTO(D2:D10;B2:B10;">=500") descarta las tres delegaciones pequeñas y devuelve 86,0%: más cerca por accidente, y sigue siendo una media de cocientes.

La versión condicional de un cociente de totales es un par de SUMAR.SI.CONJUNTO sobre los recuentos. =SUMAR.SI.CONJUNTO(C2:C10;B2:B10;">=500")/SUMAR.SI.CONJUNTO(B2:B10;B2:B10;">=500") devuelve 86,1% para esas cinco.

SUMAR.SI hace lo mismo con una sola condición, y en Microsoft 365 =PROMEDIO(FILTRAR(D2:D10;B2:B10>=500)) es la versión explícita del error, lo cual ayuda: se ve qué está promediando.

Deja en paz la división por cero. Cuando ninguna fila cumple, tanto PROMEDIO.SI.CONJUNTO como el par de SUMAR.SI.CONJUNTO devuelven #¡DIV/0!.

Ese error es la respuesta honesta a un mes sin visitas. Envolverlo en =SI.ERROR(...;0) convierte "no operamos" en "fallamos todo", y después el panel ya no distingue.

Escenario: Coge cualquier promedio filtrado de tu panel y reconstrúyelo como dos SUMAR.SI.CONJUNTO sobre los recuentos. Pon el par al lado del original, multiplica por diez el volumen de una delegación y mira cuál de los dos se mueve.

Pruébalo en la cuadrícula

09Las Tasas de Crecimiento Tienen la Misma Forma

Un mes que sube un 20% y otro que baja un 20% promedian cero. Lleva 100 € a 120 € y 120 € a 96 €, y el hecho de los dos meses es una caída del 4%.

Los porcentajes de bases distintas no se promedian, igual que no se promedian los de denominadores distintos. Encadénalos: =(96/100)^(1/2)-1 devuelve −2,0%, la tasa mensual que de verdad compone un −4%.

La forma general. Con los factores en E2:E13, =PRODUCTO(E2:E13)^(1/CONTAR(E2:E13))-1 es la tasa mensual media, y =MEDIA.GEOM(E2:E13)-1 lo dice en una función.

Un factor es 1,2 para una subida del 20% y 0,8 para una bajada del 20%. Promédialos y sale 1,0; multiplícalos y sale 0,96, que es lo que hizo la cuenta del banco. =POTENCIA(96/100;1/2)-1 es la misma aritmética para dos meses.

10La Media sin Ponderar no Está Mal, Está sin Etiquetar

Hay una pregunta que la media de la columna D responde exactamente: ¿cómo le fue a la delegación media, contando cada una una vez? Para una decisión por delegación — plantilla, la evaluación de un responsable, dónde mandar ayuda — esa es la pregunta buena.

También es la buena cuando los denominadores coinciden. Nueve delegaciones del mismo tamaño hacen que las dos fórmulas coincidan, y =MAX(B2:B10)/K.ESIMO.MENOR(B2:B10;2) te dice en una celda lo lejos que estás de eso.

Lo que falla es un titular que responde a una pregunta y se lee como la otra. Etiquetar la celda como "media de tasas por delegación, sin ponderar" cuesta seis palabras y termina la discusión antes de empezarla.

Escenario: Busca el porcentaje principal de tu panel y escribe al lado cuál de los dos es. Si no lo distingues por la fórmula en dos segundos, tampoco lo hará quien lo cite en una reunión. Después pon el otro al lado.

Pruébalo en la cuadrícula

11Cinco Controles Antes de que Salga el Panel

  1. Los dos números, juntos. =ABS(PROMEDIO(D2:D10)-SUMA(C2:C10)/SUMA(B2:B10)) devuelve aquí 0,046. Más de medio punto es un problema de ponderación, no de redondeo.
  2. Cuenta lo que contó PROMEDIO. =CONTAR(D2:D10) devuelve 8 donde =FILAS(D2:D10) devuelve 9. Una fila no está en el titular, y la hoja no dice cuál.
  3. Mide las filas. =MAX(B2:B10)/K.ESIMO.MENOR(B2:B10;2) devuelve 29,6. Una media sobre filas que se diferencian treinta veces es una media de la cosa equivocada.
  4. Cuadra la columna de porcentajes. =SUMAPRODUCTO(B2:B10;D2:D10) debería dar =SUMA(C2:C10), es decir 5.622. Si no, algún porcentaje se escribió a mano.
  5. Recalcula una delegación. =C6/B6 frente al D6 que ya está en la hoja. Un solo desajuste significa que la columna D es historia, no aritmética.

12Diez Trampas

  1. Una media de cocientes. =PROMEDIO(D2:D10) sobre una columna de porcentajes responde a una pregunta que nadie hizo, y nunca da error al hacerlo.
  2. Un cero por "sin datos". Una celda vacía se salta; un cero es un suspenso. Aquí es la diferencia entre 91,1% y 81,0%.
  3. Texto vacío de SI.ERROR. PROMEDIO lo ignora, CONTARA lo cuenta y SUMAPRODUCTO rechaza la columna entera con #¡VALOR!.
  4. PROMEDIOA como PROMEDIO seguro. Cuenta el texto como 0 y VERDADERO como 1, que en una columna de porcentajes es una delegación al 100%.
  5. Filtrar en vez de ponderar. PROMEDIO.SI.CONJUNTO reduce las filas y sigue pesando igual las que quedan.
  6. Promediar un promedio. Un trimestre hecho con tres medias mensuales es la misma trampa un nivel más arriba, y febrero pesa menos que marzo.
  7. Promediar crecimientos. Subir un 20% y bajar un 20% se informa como plano y es una caída del 4%. Usa MEDIA.GEOM sobre los factores.
  8. Filas ocultas y filtradas. PROMEDIO cuenta las filas que esconde un filtro. =SUBTOTALES(101;D2:D10) y =AGREGAR(1;5;D2:D10) no.
  9. Promediar cifras redondeadas. Una columna redondeada a un decimal, promediada, se separa de la misma columna a precisión completa. Redondea el titular, no las entradas.
  10. =SI.ERROR(PROMEDIO(D2:D10);0). Un mes roto se informa como un mes a cero, y el panel enseña la peor cifra que ha visto el contrato.

13Cinco Ejercicios

  1. Reprodúcelo. Monta las nueve filas, pon =PROMEDIO(D2:D10) y =SUMA(C2:C10)/SUMA(B2:B10) juntas y confirma 91,1% frente a 86,5%.
  2. Mueve una delegación. Cambia Dunhollow de 48 visitas a 1.480, dejándola al 100%, y mira cuál de las dos celdas se mueve.
  3. Rómpelo con un cero. Escribe 0 en D9 y vuelve a leer el titular. Luego bórralo y léelo una tercera vez.
  4. Haz fallar a SUMAPRODUCTO. Devuelve =SI.ERROR(C9/B9;"") a D9 y ejecuta la fórmula ponderada. Después protégela con ESNUMERO.
  5. Encadena el crecimiento. Pon 1,2 y 0,8 en dos celdas, promédialos, multiplícalos y decide cuál explica que 100 € acaben en 96 €.

Qué Llevarte

En este panel no había nada roto. PROMEDIO devolvió la media de los números que le dieron, la columna D era correcta hasta el último decimal, y el titular quedó por encima del umbral en un mes en que el servicio no lo estuvo.

Lo que nadie había escrito es que una media sobre filas pondera filas. Nueve delegaciones de tamaños muy distintos reparten ocho votos iguales entre 6.498 visitas, y las rutas más pequeñas votan primero.

Dos celdas lo dicen antes de que salga el informe: visitas puntuales entre visitas, y un recuento de la columna frente a las filas que tiene. Una caza la ponderación; la otra, la celda que se salió del cálculo sin avisar.

Dos celdas, frente a 38.000 € de penalizaciones en un trimestre que se informó como aprobado.

Comparte este artículo:
Volver al Blog
Reto diario · Día 107

Suma los kilómetros de un periodo de liquidación con SUMAR.SI.CONJUNTO

Un ejercicio nuevo cada día, resuelto en una cuadrícula real.

Resolver el reto de hoy