Fórmula genérica
=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
Resumen
Para promediar los últimos 5 valores de datos en un rango de columnas, puede usar la función PROMEDIO junto con las funciones COUNT y OFFSET. En el ejemplo que se muestra, la fórmula en F6 es:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))
Explicación
La función DESPLAZAMIENTO se puede usar para construir rangos dinámicos usando una celda inicial y filas, columnas, alto 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. Queremos que OFFSET devuelva un rango que comienza en la última entrada y se expande "hacia atrás", por lo que proporcionamos argumentos de la siguiente manera:
referencia - la referencia inicial es D5 - la celda directamente a la derecha de la fórmula y la primera celda en el rango de valores con los que estamos trabajando.
filas : usamos 0 para el argumento filas, porque queremos permanecer en la misma fila.
columnas : para el argumento de columnas, usamos la función COUNT para contar todos los valores en el rango, luego restamos 5. Esto desplaza el comienzo del rango 5 columnas a la izquierda.
altura : usamos 1 ya que queremos un rango de 1 fila como resultado final.
ancho : usamos 5, ya que queremos un rango final con 5 columnas.
Para la fórmula en C5, OFFSET devuelve un rango final de F5: J5. Esto entra en la función PROMEDIO que devuelve el promedio de los 5 valores en el rango
Menos de 5 valores
Si hay menos de 5 valores, la fórmula devolverá un error de referencia circular, ya que el rango volverá a extenderse a la celda que contiene la fórmula. Para evitar este error, puede adaptar la fórmula de la siguiente manera:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Aquí usamos la función MIN para "capturar" situaciones donde hay menos de 5 valores, y usamos el conteo real cuando los hay.
