Excel: cómo calcular el promedio excluyendo los valores atípicos

En el mundo empresarial actual, el análisis de datos se ha convertido en una herramienta esencial para la toma de decisiones inteligentes. Excel, el popular programa de hojas de cálculo, es utilizado por millones de personas en todo el mundo para organizar y manipular datos. ¿Sabías que Excel también te permite calcular el promedio excluyendo los valores atípicos? Esta función puede ser extremadamente útil para obtener resultados más precisos en tus análisis. En este artículo, exploraremos paso a paso cómo realizar este cálculo en Excel, ¡así que prepárate para mejorar tus habilidades en análisis de datos!


Hay dos formas de calcular un promedio excluyendo los valores atípicos en Excel:

1. Calcule el promedio y use TRIMMEAN para excluir valores atípicos

2. Calcule el promedio y utilice el rango intercuartil para excluir valores atípicos

Usaremos el siguiente conjunto de datos en Excel para ilustrar cómo usar ambos métodos:

Excel: cómo calcular el promedio excluyendo los valores atípicos

Método 1: calcular el promedio y utilizar TRIMMEAN para excluir valores atípicos

El RECORTAR La función en Excel se puede utilizar para calcular el promedio de un rango de valores excluyendo un cierto porcentaje de observaciones de la parte superior e inferior del conjunto de datos.

Por ejemplo, podemos usar la siguiente fórmula para calcular el valor promedio en la columna A excluyendo un total del 20% de las observaciones (10% desde arriba y 10% desde abajo):

=TRIMMEAN(A2:A16, 20%)

Dado que tenemos 15 valores en nuestro conjunto de datos, el 10% es 1,5, que se redondea hacia abajo a 1. Por lo tanto, esta fórmula calculará el promedio de los valores en el rango excluyendo el valor más pequeño y el valor más grande:

Excel: cómo calcular el promedio excluyendo los valores atípicos

El promedio excluyendo los valores atípicos resulta ser 58.30769.

Método 2: calcular el promedio y utilizar el rango intercuartil para excluir valores atípicos

El rango intercuartil (IQR) es la diferencia entre el percentil 75 (Q3) y el percentil 25 (Q1) en un conjunto de datos. Mide la dispersión del 50% medio de los valores.

Podemos definir una observación como un valor atípico si es 1,5 veces el rango intercuartil mayor que el tercer cuartil (Q3) o 1,5 veces el rango intercuartil menor que el primer cuartil (Q1).

Podemos usar la siguiente fórmula para calcular el rango intercuartil de nuestro conjunto de datos en Excel:

=QUARTILE(A2:A16,3)-QUARTILE(A2:A16,1)

La siguiente captura de pantalla muestra cómo utilizar esta fórmula:

Excel: cómo calcular el promedio excluyendo los valores atípicos

A continuación, podemos usar la siguiente fórmula para usar el IQR para identificar valores atípicos y asignar un “1” a cualquier valor que sea un valor atípico en el conjunto de datos:

=IF(OR(A2<QUARTILE($A$2:$A$16,1)-1.5*$B$18,A2>QUARTILE($A$2:$A$16,3)+1.5*$B$18),1,0)

La siguiente captura de pantalla muestra cómo utilizar esta fórmula:

Excel: cómo calcular el promedio excluyendo los valores atípicos

Vemos que solo un valor – 164 – resulta ser un caso atípico en este conjunto de datos.

Por último, podemos utilizar la siguiente fórmula para calcular el promedio de todos los valores del conjunto de datos que no son valores atípicos:

=AVERAGEIF(B2:B16, 0, A2:A16)

La siguiente captura de pantalla muestra cómo utilizar esta fórmula:

Excel: cómo calcular el promedio excluyendo los valores atípicos

El promedio excluyendo los valores atípicos resulta ser 55.42857.

Recursos adicionales

Los siguientes tutoriales explican cómo realizar otras tareas comunes en Excel:

Cómo calcular el rango intercuartil (IQR) en Excel
Cómo calcular el promedio por grupo en Excel
Cómo calcular medias móviles ponderadas en Excel

Error 403 The request cannot be completed because you have exceeded your quota. : quotaExceeded

Excel: cómo calcular el promedio excluyendo los valores atípicos

Excel es una herramienta poderosa que ofrece una amplia variedad de funciones para el análisis y la manipulación de datos. Uno de los cálculos más comunes que puedes realizar en Excel es el promedio. Sin embargo, en algunas ocasiones, los valores atípicos pueden sesgar significativamente el resultado de tu cálculo. Afortunadamente, Excel te permite calcular el promedio excluyendo estos valores atípicos de manera sencilla.

¿Qué es un valor atípico?

Un valor atípico, también conocido como valor extremo o outlier, es un punto de datos que se desvía significativamente del patrón general de un conjunto de datos. Estos valores pueden ser el resultado de errores de medición, datos inconsistentes o simplemente valores extremos que quedan fuera de lo esperado en un conjunto de datos.

Filtrar los valores atípicos en Excel

Para calcular el promedio excluyendo los valores atípicos en Excel, puedes utilizar la función FILTRO y la función PROMEDIO. Aquí te mostramos los pasos:

  1. Selecciona una columna vacía al lado de tus datos originales.
  2. Ingresa la siguiente fórmula en la primera celda vacía de la columna: =FILTRO(rango_de_tus_datos, rango_de_tus_datos<PROMEDIO(rango_de_tus_datos)+2*DESVIACION.TIPICA(rango_de_tus_datos))
  3. Sustituye rango_de_tus_datos por la referencia a tu propio rango de datos.
  4. Presiona Enter para aplicar la fórmula.
  5. En la siguiente celda de la columna, utiliza la función PROMEDIO para calcular el promedio de los datos filtrados: =PROMEDIO(celda_de_tu_fórmula)
  6. Presiona Enter nuevamente para obtener el resultado.

Al utilizar el filtro mencionado, los valores atípicos se excluirán del cálculo del promedio. Puedes ajustar la fórmula según tus necesidades, como cambiar el número de desviaciones estándar para definir lo que consideras como valores atípicos.

Recursos adicionales

Si deseas obtener más información sobre cómo trabajar con Excel y calcular promedios excluyendo valores atípicos, te recomendamos consultar los siguientes recursos:

¡No dejes que los valores atípicos afecten tus cálculos! Con Excel y estas funciones, podrás obtener de forma precisa el promedio excluyendo cualquier valor que pueda desviar tus resultados.

Deja un comentario