Fórmula genérica
=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
Resumo
Para calcular a média dos últimos 5 valores de dados em um intervalo de colunas, você pode usar a função AVERAGE junto com as funções COUNT e OFFSET. No exemplo mostrado, a fórmula em F6 é:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))
Explicação
A função OFFSET pode ser usada para construir intervalos dinâmicos usando uma célula inicial e linhas, colunas, altura e largura fornecidas.
Os argumentos das linhas e colunas funcionam como "deslocamentos" da referência inicial. Os argumentos de altura e largura, ambos opcionais, determinam quantas linhas e colunas o intervalo final inclui. Queremos que OFFSET retorne um intervalo que comece na última entrada e expanda "para trás", então fornecemos os argumentos a seguir:
referência - a referência inicial é D5 - a célula diretamente à direita da fórmula e a primeira célula no intervalo de valores com os quais estamos trabalhando.
linhas - usamos 0 para o argumento de linhas, porque queremos ficar na mesma linha.
colunas - para o argumento de colunas, usamos a função COUNT para contar todos os valores no intervalo e, em seguida, subtrair 5. Isso muda o início do intervalo 5 colunas para a esquerda.
altura - usamos 1, pois queremos um intervalo de 1 linha como o resultado final.
largura - usamos 5, pois queremos um intervalo final com 5 colunas.
Para a fórmula em C5, OFFSET retorna um intervalo final de F5: J5. Isso vai para a função AVERAGE, que retorna a média dos 5 valores no intervalo
Menos de 5 valores
Se houver menos de 5 valores, a fórmula retornará um erro de referência circular, pois o intervalo se estenderá de volta para a célula que contém a fórmula. Para evitar esse erro, você pode adaptar a fórmula da seguinte forma:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Aqui, usamos a função MIN para "capturar" situações em que há menos de 5 valores e usamos a contagem real quando houver.
