Excel - Promedio ultimas 5 celdas (rango se actualiza)

Mi problema es, tengo una columna (K6:K43) con números que se van rellenado semanalmente. El rango cuenta con celdas vacías por medio.

Archivo adjunto

Mi pregunta es: ¿existe alguna forma que haga un promedio de los últimos 5 valores (entre estos hay celdas vacías)?

Gracias de antemano

• promedio_ultimos_5_valores.zip(8,0 KB)

Se me ocurre una forma, pero no es directa, a ver si me explico bien:

Utilizar una columna auxiliar. En ella contar cuantos valores hay desde la fila que estás hasta el final del rango. La fórmula sería =CONTAR(A6:A$43) en B6 y arrastrarla hasta B43.

Lo que deberías hacer es buscar el valor 5, eso indicaría que desde esa fila hasta la fila 43, en la columna A hay 5 valores.
Con la función COINCIDIR puedes obtener la fila, añadiéndole una A delante tendrías la celda.

Mediante la función indirecto, puedes utilizar este resultado como referencia a una celda.

Ya sabes la celda donde está el primer valor, y sabes que como muy lejos estará en A43, por lo que con hacer el promedio de ese rango te bastaría, ya que la función promedio no tiene en cuenta las celdas vacías.

Y por último, si quieres ocultas la columna B.

Es más fácil de hacer que explicar, te dejo archivo adjunto.

saludos!

• promedio_ultimos_5_valores.rar(10,6 KB)

Muchas gracias por tu aporte, el problema me ha dado muchos quebraderos de cabeza.

Mil gracias compañero