Fórmula do Excel: Média dos últimos 5 valores -

Índice

Fórmula genérica

=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))

Resumo

Para calcular a média dos últimos 5 pontos de dados, você pode usar a função AVERAGE junto com as funções COUNT e OFFSET. Você pode usar esta abordagem para calcular a média dos últimos N pontos de dados: últimos 3 dias, últimas 6 medições, etc. No exemplo mostrado, a fórmula em F6 é:

=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))

Nota: um valor negativo para a altura não funciona no Google Sheets. Veja abaixo para mais informações.

Explicação

A função OFFSET pode ser usada para construir intervalos retangulares dinâmicos com base em uma referência 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. Para este exemplo, OFFSET é configurado assim:

  • referência = C3
  • linhas = COUNT (A: A)
  • cols = 0
  • altura = -5
  • largura = (não fornecido)

A referência inicial é fornecida como C3 a célula acima dos dados reais. Visto que queremos que OFFSET retorne um intervalo originado da última entrada na coluna C, usamos a função COUNT para contar todos os valores na coluna C para obter o deslocamento de linha necessário. COUNT conta apenas valores numéricos, portanto, o título na linha 3 é automaticamente ignorado.

Com 8 valores numéricos na coluna C, a fórmula OFFSET resolve para:

OFFSET(C3,8,0,-5)

Com esses valores, OFFSET começa em C3, desloca 8 linhas para C11, então usa -5 para estender o intervalo retangular para cima "para trás" 5 linhas para criar o intervalo C7: C11.

Finalmente, OFFSET retorna o intervalo C7: C11 para a função AVERAGE, que calcula a média dos valores naquele intervalo.

Excel x planilhas

Uma peculiaridade estranha com esta fórmula é que ela não funcionará com o Google Sheets, porque a função OFFSET no Sheets não permite um valor negativo para os argumentos de altura ou largura. A documentação do Excel também afirma que a altura ou largura não pode ser negativa, mas parece que os valores negativos funcionam bem no Excel desde os anos 1990.

Para evitar valores negativos de altura ou largura, você pode usar uma fórmula como esta:

=OFFSET(C4,COUNT(C:C)-5,0,5)

Observe que C4 é a referência inicial neste caso. A forma geral é:

=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))

onde A1 é a primeira célula nos números que você deseja calcular a média.

Artigos interessantes...