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.
| Medida | En el panel | En el contrato |
|---|---|---|
| Puntualidad del mes | 91,1% | 86,5% |
| Fórmula | =PROMEDIO(D2:D10) | =SUMA(C2:C10)/SUMA(B2:B10) |
| Puntos por debajo del 90% | ninguno | 3,5 |
| Penalización del mes | 0 € | 14.000 € |
| Penalización del trimestre | 0 € | 38.000 € |
Alcance.
PROMEDIO,PROMEDIOA,PROMEDIO.SIyPROMEDIO.SI.CONJUNTOen Excel para Microsoft 365, 2021, 2019 y Excel para Mac.PROMEDIO.SI.CONJUNTOnecesita 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.
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ón | Visitas | % puntual | Peso en =PROMEDIO(D2:D10) | Peso en las visitas |
|---|---|---|---|---|
| Ashbourne | 1.420 | 85,0% | 12,5% | 21,9% |
| Eastgate | 1.400 | 87,0% | 12,5% | 21,5% |
| Brindle Park | 96 | 100,0% | 12,5% | 1,5% |
| Dunhollow | 48 | 100,0% | 12,5% | 0,7% |
| Hestham | 0 | — | ninguno | 0,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.
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.
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.
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.
SUMAPRODUCTOdevuelve#¡VALOR!en cuantoD9contiene 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 absoluto | 91,1% | 8 |
=SI.ERROR(C9/B9;"") | 91,1% | 8 |
El texto n/d | 91,1% | 8 |
0, escrito por si acaso | 81,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.
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 celda | PROMEDIO | PROMEDIOA |
|---|---|---|
| Celda vacía | lo ignora | lo ignora |
Texto vacío de SI.ERROR | lo ignora | cuenta 0 |
| Cualquier otro texto | lo ignora | cuenta 0 |
VERDADERO | lo ignora | cuenta 1 |
0 | cuenta 0 | cuenta 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.CONJUNTOcomo el par deSUMAR.SI.CONJUNTOdevuelven#¡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.
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))-1es la tasa mensual media, y=MEDIA.GEOM(E2:E13)-1lo 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)-1es 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ícula11Cinco Controles Antes de que Salga el Panel
- 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. - 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. - 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. - 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. - Recalcula una delegación.
=C6/B6frente alD6que ya está en la hoja. Un solo desajuste significa que la columna D es historia, no aritmética.
12Diez Trampas
- 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. - 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%.
- Texto vacío de
SI.ERROR.PROMEDIOlo ignora,CONTARAlo cuenta ySUMAPRODUCTOrechaza la columna entera con#¡VALOR!. PROMEDIOAcomo PROMEDIO seguro. Cuenta el texto como 0 yVERDADEROcomo 1, que en una columna de porcentajes es una delegación al 100%.- Filtrar en vez de ponderar.
PROMEDIO.SI.CONJUNTOreduce las filas y sigue pesando igual las que quedan. - 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.
- Promediar crecimientos. Subir un 20% y bajar un 20% se informa como plano y es una caída del 4%. Usa
MEDIA.GEOMsobre los factores. - Filas ocultas y filtradas.
PROMEDIOcuenta las filas que esconde un filtro.=SUBTOTALES(101;D2:D10)y=AGREGAR(1;5;D2:D10)no. - 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.
=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
- 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%. - 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.
- Rómpelo con un cero. Escribe 0 en
D9y vuelve a leer el titular. Luego bórralo y léelo una tercera vez. - Haz fallar a
SUMAPRODUCTO. Devuelve=SI.ERROR(C9/B9;"")aD9y ejecuta la fórmula ponderada. Después protégela conESNUMERO. - 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.