EXCEL: Precisa de fórmula que retorne da última vez que um valor foi = valor atual

Question

Na minha planilha do Excel atual, tenho a data na coluna A e S & P 500 preços de fechamento na coluna B.

Estou tentando escrever uma fórmula que retorne a data da última vez em que o preço de fechamento foi maior ou igual ao preço de fechamento de hoje.

Eu tentei usar SUMPRODUCT, MATCH, VLOOKUP e INDEX, mas sem sucesso. Alguma sugestão?

Veja como minha planilha se parece:

Eu quero uma fórmula para entrar na célula B8.

    

Answer 1

Um bom primeiro passo é determinar a linha do preço de fechamento mais recente que foi maior ou igual ao preço de fechamento de hoje. Olhe para todas as linhas onde o preço de fechamento foi maior ou igual ao preço de fechamento de hoje. Em seus dados de amostra, eles são linhas 13, 14, 16, 17, 19, 20, 22, 23 e 24 (e presumivelmente mais, voltando no tempo). Qual é o mais recente? São 13, porque é o menor número de linha (mínimo) na lista.

Agora, vamos ver se podemos criar essa lista. Parece que queremos avaliar

IF( Bn >= B12, n, (nothing) )

Na verdade, podemos fazer algo semelhante:

=MIN(IF(B13:B100>=B$12, ROW(B13:B100)))

substituindo o 100 por um número maior que o maior número de linha que pode conter dados. (Eu imagino no seu caso que você poderia usar 365, ou 260 (52 × 5), ou um múltiplo de um deles se você tiver vários anos de dados.)

O acima é uma fórmula de matriz . Portanto, para fazê-lo funcionar, você deve digitar Ctrl + Deslocar + Enter depois de digitar a fórmula.

Mas você não quer o número da linha; você quer os dados. Essa parte é fácil:

=INDEX($A:$A, MIN(IF(B13:B100>B$12, ROW(B13:B100))))

Novamente, você deve digitar Ctrl + Deslocar + Enter depois de digitar a fórmula.