Fórmula genérica
=AVERAGE(INDEX(table(column),ROWS(table)-(N-1)):INDEX(table(column),ROWS(table)))
Resumo
Para calcular a média para os últimos N valores em uma tabela Excel (isto é, últimas 3 linhas, últimas 5 linhas, etc.), você pode usar a função AVERAGE junto com as funções INDEX e ROWS. No exemplo mostrado, a fórmula em F5 é:
=AVERAGE(INDEX(Table1(Sales),ROWS(Table1)-(F4-1)):INDEX(Table1(Sales),ROWS(Table1)))
Explicação
Esta fórmula é um bom exemplo de como as referências estruturadas podem tornar o trabalho com dados no Excel muito mais fácil. Basicamente, isso é o que estamos fazendo:
=AVERAGE(first:last)
onde "primeiro" é uma referência à primeira célula a ser incluída na média e "última" é uma referência à última célula a ser incluída. O resultado é um intervalo que inclui as células N para calcular a média.
Para obter a primeira célula do intervalo, usamos INDEX assim:
INDEX(Table1(Sales),ROWS(Table1)-(F4-1))
A matriz é toda a coluna Vendas e o número da linha trabalhado subtraindo (n-1) do total de linhas.
No exemplo, F4 contém 3, então o número da linha é 10- (3-1) = 8. Com um número de linha 8, INDEX retorna C12.
Para obter a última célula, usamos INDEX novamente assim:
INDEX(Table1(Sales),ROWS(Table1))
Existem 10 linhas na tabela, então INDEX retorna C14.
A função AVERAGE retorna a média de C12: C14, que é $ 78,33.
