La función PROMEDIO de Excel: los errores ocultos

PROMEDIO es una de esas funciones sencillas en Excel. ¡Súper fácil! Tiene un solo argumento, las celdas o números a promediar. ¿Entonces, cuál es el problema? Es muy fácil equivocarse y, sin embargo, es posible que no nos demos cuenta durante semanas, meses, años o nunca.

Mire la captura de pantalla a continuación. El resultado de cada método para calcular un promedio es 9, que es el resultado que esperamos.

Quizás se pregunte por qué necesitamos tantos métodos para calcular un promedio. A medida que consideramos diferentes escenarios de datos, estas fórmulas comenzarán a mostrar resultados diferentes.

La mayoría de las fórmulas son lo suficientemente simples como para entenderlas sin explicación, pero destacaré algunas de las más desconocidas:

Funciones PROMEDIOA o CONTARA

Estas funciones incluyen todas las celdas pobladas, en lugar de solo las celdas numéricas.

Fórmulas de matriz

Las fórmulas con {Array} en su descripción son fórmulas especiales. Ingrese estos presionando Ctrl + Shift + Enter, lo que agrega llaves al principio y al final. No escriba las llaves; Excel los insertará él mismo.

Estas fórmulas realizan un cálculo en una matriz de celdas antes de continuar. Mira el ejemplo de abajo:

{=PROMEDIO(B2:B13*1)}

Cada celda en el rango B2-B13 se multiplica por 1 antes de proceder al promediado. Es la multiplicación de cada celda dentro de una única fórmula lo que la convierte en una fórmula matricial.

Haga clic aquí para obtener más información sobre las fórmulas matriciales .

Función agregada

La función AGREGAR incluye la capacidad de calcular un promedio excluyendo elementos específicos, como errores o filas ocultas.

Haga clic aquí para obtener más información sobre la función AGREGAR .

Ahora analicemos algunos escenarios para mostrar cómo pequeños cambios en los datos pueden causar estragos en los resultados.

Problema n.º 1: texto incluido en el conjunto de datos

En la captura de pantalla siguiente, el conjunto de datos ha cambiado; la cadena de texto "Ninguno" ha reemplazado los valores cero.

El resultado de las fórmulas promedio ahora incluye una combinación de valores.

Las fórmulas que incluyen CONTAR, PROMEDIO y AGREGAR se calculan como 12, en lugar de 9, ya que estas funciones ignoran los valores de texto. Como resultado, el total se divide por las celdas que contienen números, en lugar del número total de celdas.

COUNTA y AVERAGEA incluyen celdas de texto dentro del cálculo, por lo tanto, el resultado del cálculo permanece en 9.

La función PROMEDIO {Matriz} multiplica cada valor por 1 antes de aplicar el promedio. Provocando un error, ya que una cadena de texto no se puede multiplicar por un número.

La fórmula IFERROR PROMEDIO {Matriz} calcula el valor correcto de 9. La función IFERROR calcula primero, forzando cualquier error a cero, por lo tanto, cuando se aplica el promedio, no quedan valores de texto.

Problema nº 2: celdas en blanco incluidas en el conjunto de datos

En la captura de pantalla siguiente, el conjunto de datos ha cambiado; los ceros del ejemplo original se reemplazan con celdas en blanco.

El resultado de las fórmulas promedio incluye solo dos resultados 9 o 12.

Las funciones CONTAR, CONTARA, PROMEDIO, PROMEDIOA y AGREGAR excluyen las celdas en blanco en el recuento de celdas. Provocando que estas fórmulas se calculen incorrectamente.

Las fórmulas PROMEDIO { Matriz } y PROMEDIO IFERROR { Matriz } multiplican las celdas en blanco por 1, lo que las obliga a valores cero antes de calcular el promedio. El resultado del cálculo de estas fórmulas sigue siendo correcto en 9.

Problema nº 3: números formateados como texto

En este último ejemplo, el valor de la celda B4 tiene formato de texto.

Hay dos problemas potenciales aquí:

• El valor total puede excluir el número formateado como texto.
• El recuento de celdas por el que se va a dividir puede excluir la celda de texto.

Las funciones CONTAR, PROMEDIO y AGREGAR excluyen el texto dentro del recuento de celdas, lo que provoca que un total incorrecto se divida entre 11, en lugar de 12 celdas (es decir, se han producido ambos problemas)

Las funciones CONTAR, PROMEDIOA incluirán el texto al calcular el recuento de celdas, pero no en el total. Por lo tanto, el valor incorrecto se divide por el número correcto de celdas (es decir, ha ocurrido el primer problema).

Las dos funciones {Array} calculan el valor correcto. Cualquier número formateado como texto se multiplica por 1, convirtiendo el texto en un número; manteniendo el valor total y el recuento de células.

Comparación de todas las fórmulas.

¿Qué tan bien funcionó cada fórmula en todos los escenarios?

Solo hay una fórmula que calcula el resultado correcto en todos los escenarios, la fórmula PROMEDIO/IFERROR {matriz}.

Vale la pena señalar que, en esta publicación, he hecho una suposición importante. He asumido que queremos calcular el mismo resultado que los datos originales. Pero ¿qué pasa si queremos excluir celdas en blanco o valores de texto? La función de matriz que se calculó correctamente ya no cumple con nuestros requisitos. Tendríamos que elegir otra fórmula.

No existe una fórmula perfecta para todos los escenarios, ya que mucho depende de la intención del usuario.

Conclusión

Hay muchas formas de calcular un promedio. Diferentes fórmulas se adaptarán a diferentes conjuntos de datos y escenarios. Lo fundamental es conocer sus datos y exactamente lo que quiere lograr .

Si no tiene control sobre el conjunto de datos, considere agregar una fórmula de verificación. Por ejemplo, la fórmula ESNÚMERO se calculará como VERDADERO si todas las celdas del conjunto de datos son números; de lo contrario, se calculará como FALSO. Al agregar la función SI, la siguiente fórmula mostraría "Texto incluido en el conjunto de datos" si alguna celda en el rango B2-B13 no fuera un número.

=SI(ESNÚMERO(B2:B13),PROMEDIO(B2:B13),"Texto incluido en el conjunto de datos")

---