Calcula la Media Móvil Simple de tus Últimos N Resultados Financieros en Excel

En el análisis financiero y de datos, la Media Móvil Simple (MMS) es una herramienta fundamental para suavizar las fluctuaciones a corto plazo y destacar tendencias o ciclos a largo plazo. Calcular la media de los últimos «N» períodos (días, meses, trimestres) es una tarea recurrente. Sin embargo, actualizar manualmente el rango de celdas cada vez que se añade un nuevo dato es tedioso y propenso a errores.

Afortunadamente, Excel nos ofrece una solución robusta y dinámica. Mediante una combinación inteligente de las funciones PROMEDIO, INDICE, MAX y CONTARA, podemos crear una fórmula que calcula automáticamente la media móvil de los últimos N resultados, adaptándose dinámicamente a medida que añadimos nuevos datos a nuestro conjunto.

Sintaxis

=PROMEDIO(INDICE(rango_resultados, MAX(1, CONTARA(rango_resultados)-numero_periodos+1)):INDICE(rango_resultados, CONTARA(rango_resultados)))

Esta fórmula puede parecer compleja a primera vista, pero en realidad es una construcción lógica de varias funciones que trabajan en conjunto para definir un rango dinámico. Vamos a desglosarla:

• rango_resultados Es el rango de celdas (normalmente una columna) que contiene la serie de datos históricos que queremos analizar. Por ejemplo, C2:C100.
• numero_periodos Es un número que especifica cuántos de los últimos períodos se incluirán en el cálculo de la media móvil. Puede ser un número escrito directamente en la fórmula o una referencia a una celda (ej. E1), lo que permite cambiarlo fácilmente.
• CONTARA(rango_resultados) Esta función cuenta el número de celdas no vacías en rango_resultados. Nos dice cuántos puntos de datos tenemos en total.
• INDICE(…, CONTARA(…)) La segunda función INDICE crea una referencia a la última celda con datos en nuestro rango. Por ejemplo, si hay 12 datos, apunta a la celda número 12.
• INDICE(…, MAX(1, …)) La primera función INDICE es la más elaborada. Crea una referencia a la celda de inicio de nuestro rango para el promedio. La fórmula CONTARA(rango_resultados)-numero_periodos+1 calcula la posición de inicio. La función MAX se asegura de que este número nunca sea menor que 1, evitando errores si el número de períodos es mayor que la cantidad de datos disponibles.
• … : … El operador de dos puntos (:) toma las dos referencias de celda creadas por las funciones INDICE (el inicio y el fin) y construye un rango completo. Por ejemplo, C10:C12.
• PROMEDIO(…) Finalmente, la función PROMEDIO toma este rango dinámico recién creado y calcula la media aritmética de sus valores.

Ejemplos

Para ilustrar el uso de esta fórmula, imaginemos que tenemos una tabla con los ingresos mensuales de una empresa durante el último año.

Ejemplo 1: Calcular la media móvil de los últimos 3 meses

Queremos calcular el promedio de ingresos de los últimos tres meses registrados (Abril, Mayo y Junio). Definimos el número de períodos en la celda D1.

'Celda D1
3

'Fórmula para el cálculo de la media móvil
=PROMEDIO(INDICE(B:B, MAX(1, CONTARA(B:B)-D1+1)):INDICE(B:B, CONTARA(B:B)))

'Resultado esperado: 32,400
'Cálculo: (29,000 + 33,200 + 35,000) / 3

La fórmula identifica que hay 7 valores en la columna B (incluyendo el encabezado), por lo que CONTARA(B:B) devuelve 7. La última celda con datos es B7. El punto de inicio es 7 - 3 + 1 = 5. Por tanto, la fórmula calcula el promedio del rango B5:B7.

Ejemplo 2: Comportamiento dinámico al añadir nuevos datos

La principal ventaja de esta fórmula es su dinamismo. Si añadimos los ingresos de Julio en la celda B8, la fórmula se recalcula automáticamente sin necesidad de ninguna modificación.

'La misma fórmula del Ejemplo 1, sin cambios:
=PROMEDIO(INDICE(B:B, MAX(1, CONTARA(B:B)-D1+1)):INDICE(B:B, CONTARA(B:B)))

'Nuevo resultado esperado: 34,766.67
'Cálculo: (33,200 + 35,000 + 36,100) / 3

Ahora, CONTARA(B:B) devuelve 8. El nuevo rango dinámico es B6:B8, correspondiente a Mayo, Junio y Julio.

Ejemplo 3: Manejo de menos datos que los períodos solicitados

Supongamos que solo tenemos datos de Enero y Febrero, pero solicitamos una media móvil de 5 períodos. Gracias a la función MAX, la fórmula no producirá un error.

'Datos solo en B2 y B3. Celda D1 = 5
=PROMEDIO(INDICE(B:B, MAX(1, CONTARA(B:B)-D1+1)):INDICE(B:B, CONTARA(B:B)))

'Resultado esperado: 26,250
'Cálculo: (25,000 + 27,500) / 2

En este caso, CONTARA(B:B) es 3 (incluyendo cabecera). El cálculo del inicio sería 3 - 5 + 1 = -1. Sin embargo, MAX(1, -1) devuelve 1, que corresponde a la primera fila. La fórmula ajusta el inicio a la primera celda con datos (B2), y promedia todos los datos disponibles (B2:B3).

Aplicaciones Prácticas

• Análisis FinancieroSeguimiento de la media móvil de precios de acciones, volúmenes de negociación o ingresos de la empresa para identificar la dirección de la tendencia principal.
• Gestión de VentasCalcular las ventas promedio de los últimos 7, 30 o 90 días para suavizar picos y valles estacionales y obtener una imagen más clara del rendimiento de ventas.
• Control de InventarioEstimar la demanda futura calculando el consumo medio de un producto en los últimos N períodos para optimizar los niveles de stock.
• Métricas de MarketingAnalizar el rendimiento de campañas digitales, como el promedio móvil de clics o conversiones diarias, para evaluar su efectividad a lo largo del tiempo.

Observaciones

Es importante tener en cuenta algunos detalles para el correcto funcionamiento de esta fórmula:

• Datos Contiguos: La fórmula asume que los datos en rango_resultados están en un bloque continuo, sin celdas vacías de por medio. Una celda vacía en medio del rango puede provocar que CONTARA devuelva un número incorrecto para determinar la última celda.
• Orden Cronológico: Para que una media móvil tenga sentido, los datos deben estar ordenados cronológicamente, con el dato más reciente al final de la lista.
• Encabezados: Si el rango_resultados incluye un encabezado de texto (como en nuestros ejemplos B:B), CONTARA lo incluirá en su cuenta. La fórmula se autoajusta correctamente, pero es una buena práctica ser consciente de ello. Si el encabezado fuera numérico, afectaría al cálculo.

Errores comunes

• #¡REF! Se produce si rango_resultados es una referencia no válida o si los cálculos internos de INDICE resultan en una referencia de celda inválida.
• #¡VALOR! Ocurre si el argumento numero_periodos no es un valor numérico.
• #¡DIV/0! Este error lo devolvería PROMEDIO si el rango dinámico generado no contiene ningún valor numérico (por ejemplo, si rango_resultados solo contiene texto o está vacío).

Alternativas

Aunque la combinación con INDICE es muy eficiente, existen otras formas de lograr un resultado similar:

• =PROMEDIO(DESREF(INDICE(rango;CONTARA(rango));-numero_periodos+1;0;numero_periodos))

  Esta alternativa utiliza la función DESREF. Funciona perfectamente, pero DESREF es una función «volátil», lo que significa que se recalcula cada vez que se produce cualquier cambio en la hoja de cálculo, no solo cuando cambian sus celdas precedentes. En libros de trabajo muy grandes y complejos, un uso excesivo de funciones volátiles puede afectar al rendimiento.