Fórmula genérica
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))
Resumo
Para calcular a média dos últimos 3 valores numéricos em um intervalo, você pode usar uma fórmula de matriz baseada em uma combinação de funções para alimentar os últimos n valores numéricos na função MÉDIA. No exemplo mostrado, a fórmula em D6 é:
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))
onde "dados" é o intervalo nomeado B5: B13.
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter.
Explicação
A função AVERAGE irá calcular uma média dos números apresentados em uma matriz, então quase todo o trabalho nesta fórmula é gerar uma matriz dos últimos 3 valores numéricos em um intervalo. Trabalhando de dentro para fora, a função IF é usada para "filtrar" valores numéricos:
IF(ISNUMBER(data),ROW(data))
A função ISNUMBER retorna TRUE para valores numéricos e FALSE para outros valores (incluindo espaços em branco), e a função ROW retorna números de linha, então o resultado desta operação é uma matriz de números de linha que corresponde a entradas numéricas:
(5;6;FALSE;8;9;10;FALSE;12;13)
Esta matriz vai para a função LARGE com a constante de matriz (1,2,3) para k. LARGE ignora automaticamente os valores FALSE e retorna uma matriz com os 3 números maiores, que correspondem às 3 últimas linhas com valores numéricos:
(13,12,10)
Essa matriz vai para a função LOOKUP como o valor de pesquisa. A matriz de pesquisa é fornecida pela função ROW e a matriz de resultado é o intervalo nomeado "dados":
LOOKUP((13,12,10), ROW(data), data))
LOOKUP então retorna uma matriz contendo valores correspondentes em "dados", que são alimentados em AVERAGE:
=AVERAGE((100,92,90))
Lidando com menos valores
Se o número de valores numéricos cair abaixo de 3, esta fórmula retornará o erro #NUM, já que LARGE não poderá retornar 3 valores conforme solicitado. Uma maneira de lidar com isso é substituir a constante de matriz embutida em código (1,2,3) por uma matriz dinâmica criada usando INDIRETO como este:
ROW(INDIRECT("1:"&MIN(3,COUNT(data))))
Aqui, MIN é usado para definir o limite superior da matriz para 3 ou a contagem real de valores numéricos, o que for menor.
Nota: Encontrei essa abordagem inteligente em chandoo.org, em uma resposta de Sajan a uma pergunta semelhante.
