EXCEL: Promedio de los últimos 5 valores

Para promediar los últimos 5 puntos de datos, puede usar la función PROMEDIO junto con las funciones CONTAR y DESREF. Puede usar este enfoque para promediar los últimos N puntos de datos: últimos 3 días, últimas 6 mediciones, etc. En el ejemplo que se muestra, la fórmula en F6 es:

=PROMEDIO(DESREF(C3,CONTAR(C:C),0,-5))

Nota: un valor negativo para la altura no funcionará en las hojas de Google. Vea abajo para más información.

Cómo funciona esta fórmula

La función DESREF se puede usar para construir rangos rectangulares dinámicos basados ​​en una referencia inicial y filas, columnas, altura y ancho dados. Los argumentos de filas y columnas funcionan como "desplazamientos" de la referencia inicial. Los argumentos de alto y ancho (ambos opcionales) determinan cuántas filas y columnas incluye el rango final. Para este ejemplo, DESREF se configura así:

• referencia = C3
• filas = CONTAR(A:A)
• columnas =  0
• alto = -5
• ancho = (no proporcionado)

La referencia inicial se proporciona como C3 la celda sobre los datos reales. Como deseamos que DESREF devuelva un rango que se origina en la última entrada en la columna C, usamos la función CONTAR para contar todos los valores en la columna C para obtener el desplazamiento de fila requerido. CONTAR solo cuenta valores numéricos, por lo que el encabezado de la fila 3 se ignora automáticamente.
Con 8 valores numéricos en la columna C, la fórmula DESREF resuelve:

DESREF(C3,8,0,-5)

Con estos valores, DESREF comienza en C3, desplaza 8 filas a C11, luego utiliza -5 para extender el rango rectangular hacia arriba "hacia atrás" 5 filas para crear el rango C7: C11.
Finalmente, DESREF devuelve el rango C7: C11 a la función PROMEDIO, que calcula el promedio de los valores en ese rango.

Excel vs hojas

Una peculiaridad extraña con esta fórmula es que no funcionará con las Hojas de cálculo de Google, porque la función DESREF en Hojas no permitirá un valor negativo para los argumentos de altura o anchura. La documentación de Excel también indica que la altura o el ancho no pueden ser negativos, pero por lo que podemos decir, los valores negativos han funcionado bien desde Excel 5, que se envió a principios de la década de 1990.
Para evitar este problema en Hojas de cálculo o para evitar valores negativos de altura o anchura en Excel, puede usar:

=DESREF(C4,CONTAR(C:C)-5,0,5)

El aviso C4 es la referencia inicial en este caso. La forma general es:

=PROMEDIO(DESREF(A1,CONTAR(A:A)-N,0,N))

donde A1 es la primera celda en los números que desea promediar.