Excel: usando uma média calculada de uma coluna em células na mesma coluna

Question

Eu tenho os seguintes dados:

    Column B    Column C
11  21 Oct      0.87%
12  22 Oct      1.38%
13  23 Oct      0.04%
14  24 Oct      0%
15  25 Oct      0%

E assim por diante para cada data, com 0% para todas as datas que ainda não ocorreram. Eu também tenho uma célula (C2) que contém a seguinte fórmula:

{=AVERAGE( IF(B5:B373 < TODAY(); C5:C373))}

Que calcula a média de todos os valores na coluna C até e incluindo aquele para a data de hoje (na coluna B), para que todos os valores de 0% não sejam incluídos no cálculo. Então, digamos que hoje seja 23 de outubro, então C2 teria o valor de 0,76%.

A cada dia, eu insiro uma porcentagem para essa data na célula correspondente, assim, eventualmente, todas as células 0% serão substituídas pelos valores apropriados.

Em vez de 0%, quero executar alguns cálculos preditivos com base na porcentagem média até o momento. Então, basicamente, eu quero todas as células que agora contêm 0% porque eu ainda não inseri os valores reais para conter o valor em C2, fazendo com que pareça assim:

    Column B    Column C
11  21 Oct      0.87%  < literal value
12  22 Oct      1.38%  < literal value
13  23 Oct      0.04%  < literal value
14  24 Oct      0.76%  < =C2
15  25 Oct      0,76%  < =C2

No entanto, isso não funcionará, porque criaria uma referência circular entre a fórmula nas células e a fórmula em C2.

Então eu preciso de uma fórmula para C2 que use um intervalo até e incluindo hoje . A fórmula que eu tenho agora usa o intervalo inteiro e, em seguida, usa o IF para excluir os valores que estão no futuro, o que significa que não posso usar o resultado nas outras células da coluna. Se eu tivesse uma fórmula que limitasse o intervalo para todas as células até e inclusive as de hoje, seria possível usar os resultados em todas as células fora desse intervalo. Eu tenho brincado com ÍNDICE e CORRESPONDÊNCIA, mas não consigo entender. O que estou tentando fazer é possível?

    

Answer 1

Você pode usar MATCH para descobrir até que ponto a data de datas é a data de hoje.

=MATCH(TODAY(),B5:B373,0)

Você pode usar esse número com OFFSET para obter um intervalo da coluna C que contém apenas os valores até hoje.

=OFFSET(C5,0,0,MATCH(TODAY(),B5:B373,0))

Por fim, você obtém a média dos valores nesse intervalo. Digite o seguinte na célula C2:

=AVERAGE(OFFSET(C5,0,0,MATCH(TODAY(),B5:B373,0)))

Agora você pode inserir =C$2 em cada um dos valores das datas futuras sem obter uma referência circular.