Desviación media en Excel: fórmula, cálculo y ejemplo

La desviación media mide, en promedio, cuánto se alejan los datos de su media. En una hoja con las notas de cinco alumnos te sirve para ver de un vistazo qué grupo es más homogéneo y quién se descuelga. En este tutorial la calculas primero con columnas a la antigua (notas, desviación de cada una y promedio de esas desviaciones), después con una sola fórmula que cabe en una celda y, por último, con los datos agrupados por frecuencias. Todo funciona en cualquier Excel moderno: las funciones que usamos existen desde versiones antiguas y no necesitan Microsoft 365.

Qué es la desviación media y para qué te sirve en una hoja

Imagina la hoja Notas 1ºA con las cinco calificaciones del primer examen: 5, 7, 8, 6 y 9. La media aritmética vale 7 y, a simple vista, ya ves que el 5 y el 9 están en los extremos. Pero, ¿cuánto se desvía, en promedio, cada nota del 7? Eso es exactamente la desviación media: el promedio de las distancias (en valor absoluto) entre cada valor y la media del conjunto.

Sirve para comparar la dispersión de dos grupos con la misma media. Dos clases pueden tener un 7 de media y, sin embargo, en una todas las notas rondan el 7 y en la otra hay mucha gente suspendiendo y mucha gente con matrículas muy alta. La desviación media te lo dice con un solo número: cuanto más alto, más dispersos están los datos.

En tu hoja, la desviación media de esas cinco notas te da 2,00 (las distancias a la media son 2, 0, 1, 1 y 2; su promedio es 6/3 dividido entre 5, es decir, 1,20). Lo dejamos calculado paso a paso en las siguientes secciones.

La fórmula y cada pieza que la compone

La fórmula general es:

DESVIACIÓN MEDIA = Σ|Xi – Media| / n

Se lee así: a cada dato Xi le restas la media del conjunto; si sale negativo, te quedas con su valor positivo (el valor absoluto); sumas todas esas distancias y, por último, divides entre n, el número total de datos. El símbolo Σ (sumatorio) significa "suma de todo lo que viene detrás".

Las tres piezas son: Xi (cada dato), Media (el promedio del grupo, calculado con PROMEDIO) y n (cuántos datos tienes, calculado con CONTARA porque ignora las celdas vacías pero no los ceros). Para el valor absoluto se usa la función ABS.

ABS: el valor absoluto que se queda con el signo positivo

La función ABS (en inglés, ABS) devuelve un número sin signo: si le pasas -3, devuelve 2; si le pasas 3, devuelve 3.

=ABS(número)

En Excel no se escribe el valor absoluto con las barras verticales |x| como en los libros de Matemáticas: se hace con la función, porque las barras no sirven en una fórmula.

PROMEDIO: la media aritmética del conjunto

PROMEDIO (en inglés AVERAGE) devuelve la media aritmética de un rango o de una lista de números:

=PROMEDIO(número1; [número2]; ...)

Con los datos en B2:B6, escribes =PROMEDIO(B2:B6). PROMEDIO ignora las celdas vacías, pero NO ignora los ceros: un cero en la lista cuenta como un cero y baja la media.

Calcular la desviación media con fórmulas en columnas

Vamos a la hoja Notas 1ºA. Tienes los datos en B2:B6 y vas a añadir tres columnas: la desviación de cada nota respecto a la media, la suma de esas desviaciones y, por último, la desviación media del grupo.

Construir la tabla auxiliar en cuatro pasos

1. En D2 escribes =PROMEDIO(B2:B6) y pulsas Entrar. Te muestra 7, la media del grupo.
2. En C2 escribes =ABS(B2-$D$2) y pulsas Entrar. Te muestra 2, que es |5 - 7|.
3. Seleccionas C2, sitúas el cursor sobre el controlador de relleno (el cuadradito de la esquina inferior derecha) y arrastras hasta C6. La fórmula se copia y las celdas muestran 0, 1, 1 y 2.
4. En E2 escribes =SUMA(C2:C6)/CONTARA(B2:B6) y pulsas Entrar. Te muestra 1,20, la desviación media.

Anclar la celda de la media con F4 al copiar la fórmula

La fórmula de C2 es =ABS(B2-$D$2). El $ delante de la D y delante del 2 convierte la referencia en absoluta: al copiar hacia abajo, B2 se mueve a B3, B4… pero $D$2 siempre apunta a la misma celda, la de la media.

Si no hubieras puesto los $, al copiar la fórmula a C3 tendrías =ABS(B3-D3), y como D3 está vacía, se interpreta como 0: la fórmula devolvería =ABS(B3-0) y se rompería el cálculo sin avisar.

Para poner los $, selecciona C2, entra en la barra de fórmulas (F2 o clic en la barra), coloca el cursor sobre la referencia D2 y pulsa F4 (en Mac, Cmd+T). Cada pulsación va cambiando entre relativa (A1), mixta por fila (A$1), mixta por columna ($A1) y absoluta de nuevo ($A$1). En tu caso necesitas la última.

Reducir el resultado a una sola fórmula en una celda

Si no quieres ver las columnas auxiliares, la desviación media cabe en una sola fórmula. En E2 borras lo que hay y escribes:

=SUMAPRODUCTO(ABS(B2:B6-PROMEDIO(B2:B6)))/CONTARA(B2:B6)

SUMAPRODUCTO (en inglés SUMPRODUCT) trabaja con matrices: aquí le dices que reste la media a cada nota, que tome el valor absoluto de cada resta y que sume todos esos resultados. Luego divides entre CONTARA, que cuenta cuántos datos hay en B2:B6. El resultado es 1,20, igual que antes.

Calcularla en un solo tiro sin columnas auxiliares

La fórmula del apartado anterior resuelve el cálculo en una sola celda y no necesitas arrastrar nada. Lo único que cambias al cambiar los datos es el rango: si las notas pasan a estar en B2:B10, solo tienes que ajustar los rangos.

Datos agrupados: la fórmula con frecuencias

Cuando los datos están agrupados (por ejemplo, cuántas personas sacaron cada nota), la fórmula se modifica: en vez de sumar las distancias de cada dato individual, sumas la distancia de cada valor multiplicada por las veces que se repite.

DESVIACIÓN MEDIA = Σ|Xi – Media| · fi / Σfi

En la hoja Notas agrupadas tienes los valores en B2:B9 y las frecuencias (cuántos alumnos sacaron esa nota) en C2:C9. La media del grupo se calcula con =SUMAPRODUCTO(B2:B9;C2:C9)/SUMA(C2:C9) y vale 6,625. La desviación media sale con:

=SUMAPRODUCTO(ABS(B2:B9-PROMEDIO_PONDERADO);C2:C9)/SUMA(C2:C9)

En la práctica, lo más fácil es dejar la media calculada en D2 y escribir en E2:

=SUMAPRODUCTO(ABS(B2:B9-$D$2);C2:C9)/SUMA(C2:C9)

Por qué la fórmula da 0 o #¡VALOR! sin avisar

Tres errores típicos al calcular la desviación media: olvidar el $ y que la fórmula se vaya a una celda vacía, esconder un texto entre los números, y dejar una celda vacía que PROMEDIO sí ignora pero la fórmula simple no. Vamos a verlos uno por uno.

La celda de la media se movió al copiar y todo da 0

Si copias la fórmula =ABS(B2-D2) desde C2 hasta C3:C6 sin haber puesto los $, cada fila apunta a su propia celda de la columna D (que está vacía). Una celda vacía en una operación se interpreta como 0, así que la fórmula devuelve =ABS(B3-3) con la nota, no la distancia a la media. Resultado: ves números, pero son las notas, no las desviaciones.

Solución: vuelve a C2, edita la fórmula para que la referencia a la media sea $D$2 (con F4/Cmd+T sobre la referencia), y vuelve a copiar hacia abajo.

Hay un texto escondido entre los números

Si una de las celdas tiene un espacio, la palabra "N/A" o cualquier texto, ABS devuelve #¡VALOR! y la suma entera falla. Para localizar al culpable celda a celda, en una celda auxiliar escribes =ESNUMERO(B2) y la copias hacia abajo: donde veas FALSO, ahí está el problema.

Lo que ya sabes hacer con la desviación media

• Distingues la desviación media de la desviación típica: la media usa distancias en valor absoluto, la típica las eleva al cuadrado.
• Calculas la desviación media en una hoja con columnas auxiliares (la fórmula larga, fácil de seguir paso a paso).
• La reduces a una sola fórmula con SUMAPRODUCTO, ABS y PROMEDIO cuando no quieres ver columnas extra.
• La adaptas a datos agrupados multiplicando cada distancia por su frecuencia.
• Reconoces los tres errores más comunes: el $ que falta, el texto escondido entre los números y la celda vacía que se interpreta como cero.

El siguiente paso natural es comparar la desviación media de tres cursos en la misma hoja: pones los datos de 1ºA, 1ºB y 1ºC en columnas contiguas, escribes la fórmula en cada bloque y, de un vistazo, ves qué clase es más homogénea.

Dudas al calcular la desviación media en Excel