Obtendo uma fórmula para referenciar um intervalo variável

Question

Então, estou tentando obter uma equação para trabalhar em um número variável de células. Então, o que eu tenho é uma folha com custos atuais e custos projetados. Eu gostaria que o número de previsão funcionasse apenas com os custos atuais. Isso é indicado por um ACT no início do mês. Minha fórmula inicial de sumifs / countifs foi rejeitada por gerar um erro de referência circular, então estou pensando se existe uma maneira de ter equações dinamicamente atualizadas dependendo de outra célula.

Eu prefiro evitar o uso de intervalos dinâmicos, já que estamos falando de ~ 400 linhas que precisam ser atualizadas, e eu entendo que os intervalos precisam ser adicionados um de cada vez.

Um exemplo de algo que representa o que eu espero que a fórmula faça: = Sum (A1: A (1 + B1)), em que o intervalo aumenta conforme B1 aumenta.

Dados de amostra:

    Act   Act   Act   Proj   Proj  
A:   1     2     3      2      2
B:   1     3     5      3      3
C:   4     5     6      5      5

• Projeção A: 2
• Projeção B: 3
• Projeção C: 5

Então, quando eu adiciono os números reais do 4º mês e mudo o Proj para Act, o intervalo no qual a projeção A, a projeção B, etc. calculam as mudanças de 3 a 4, sem nenhuma entrada adicional de mim.

    

Answer 1

Você pode usar OFFSET() para criar uma referência a um intervalo X-a-Y de colunas, por exemplo:

=OFFSET(A1,0,0,10,10) criará uma referência de 10 por 10 que se refere a A1:J10 .

Você pode substituir os 10s codificados na fórmula acima por COUNTIF() , que infere quantas linhas e colunas o intervalo deve se referir.

Para evitar um erro de circularidade, você pode tentar definir um Intervalo Nomeado ( Alt M M D ) com esta fórmula e usando o intervalo nomeado onde quer que você use a fórmula.

Alternativamente, você pode criar uma string de texto com a referência X-by-Y usando OFFSET() sem os parâmetros width e height (ou seja, os 10s da fórmula acima) e, em vez disso, encontrando a primeira e última célula em um alcance. Em seguida, envolva-os em uma chamada CELL("address",...) que retornará seu endereço. (NB: Se o seu ponto de partida é $ A $ 1 você só precisa encontrar a célula final).

Concatene-os com um : entre eles e envolva tudo em torno de um INDIRECT() onde quer que você precise usá-lo. Em suma, supondo uma inicialização codificada em $A$1 :

=INDIRECT("Sheet1!$A$1:"&CELL("address",OFFSET(Sheet1!$A$1,COUNTIF(..x-rows..),COUNTIF(..y-cols..))

COUNTIF() logic para encontrar o número de linhas e colunas restantes como um exercício para o leitor