Fórmula genérica
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
Resumen
Para promediar los últimos 5 puntos de datos, puede usar la función PROMEDIO junto con las funciones CONTAR y DESPLAZAR. 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:
=AVERAGE(OFFSET(C3,COUNT(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.
Explicación
La función DESPLAZAMIENTO se puede utilizar 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 "compensaciones" 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, OFFSET se configura así:
- referencia = C3
- filas = COUNT (A: A)
- cols = 0
- altura = -5
- ancho = (no incluido)
La referencia inicial se proporciona como C3, la celda sobre los datos reales. Como queremos que OFFSET devuelva un rango que se origina en la última entrada en la columna C, usamos la función COUNT para contar todos los valores en la columna C para obtener el desplazamiento de fila requerido. COUNT cuenta solo valores numéricos, por lo que el título de la fila 3 se ignora automáticamente.
Con 8 valores numéricos en la columna C, la fórmula OFFSET se resuelve en:
OFFSET(C3,8,0,-5)
Con estos valores, OFFSET comienza en C3, desplaza 8 filas a C11, luego usa -5 para extender el rango rectangular hacia arriba "hacia atrás" 5 filas para crear el rango C7: C11.
Finalmente, OFFSET devuelve el rango C7: C11 a la función PROMEDIO, que calcula el promedio de los valores en ese rango.
Excel frente a hojas
Una peculiaridad extraña de esta fórmula es que no funcionará con Hojas de cálculo de Google, porque la función DESPLAZAMIENTO en Hojas de cálculo no permitirá un valor negativo para los argumentos de altura o ancho. La documentación de Excel también indica que la altura o el ancho no pueden ser negativos, pero parece que los valores negativos han funcionado bien en Excel desde la década de 1990.
Para evitar valores negativos de alto o ancho, puede usar una fórmula como esta:
=OFFSET(C4,COUNT(C:C)-5,0,5)
Observe que C4 es la referencia inicial en este caso. La forma general es:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
donde A1 es la primera celda de los números que desea promediar.
