EXCEL: Promedio de los últimos 5 valores en columnas

Para promediar los últimos 5 valores de datos en un rango de columnas, puede usar la función PROMEDIO junto con las funciones CONTAR y DESREF. En el ejemplo que se muestra, la fórmula en F6 es:

=PROMEDIO(DESREF(D5,0,CONTAR(D5:J5)-5,1,5))

Cómo funciona esta fórmula

La función DESREF se puede usar para construir rangos dinámicos usando una celda 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. Queremos que DESREF 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: utilizamos 0 para el argumento de filas, porque queremos permanecer en la misma fila.

columnas: para el argumento de columnas, usamos la función CONTAR para contar todos los valores en el rango, luego resta 5. Esto desplaza el comienzo del rango 5 columnas a la izquierda.

altura: utilizamos 1 ya que queremos un rango de 1 fila como resultado final.

ancho: utilizamos 5, ya que queremos un rango final con 5 columnas.

Para la fórmula en C5, DESREF 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 se extenderá nuevamente dentro de la celda que contiene la fórmula. Para evitar este error, puede adaptar la fórmula de la siguiente manera:

=PROMEDIO(DESREF(first,0,CONTAR(rng)-MIN(N,CONTAR(rng)),1,MIN(N,CONTAR(rng))))

Aquí usamos la función MIN para "atrapar" situaciones donde hay menos de 5 valores, y usamos el conteo real cuando hay.