Excel 2010: fórmula para soma e data de retorno

Question

Eu tenho uma pasta de trabalho com uma planilha Resumo e uma planilha de conjunto de dados com uma tabela vinculada que contém 700k + registros de dados financeiros sobre investimentos. (No meu caso particular, são propriedades imobiliárias; mas esse é o mesmo cenário que acompanhar ações e dividendos.) A Folha de Resumo contém uma linha por propriedade (investimento) e mostra a quantidade (de dinheiro) originalmente injetada (ou seja, entrada ou investido) por propriedade, com a expectativa de que a saída monetária (ou retorno sobre o investimento) será em algum momento igual à quantidade original de injeção / investimento. O conjunto de dados mostra a produção diária (ou seja, saída monetária) por propriedade, com uma linha por propriedade por data (ou seja, tem muitas linhas para cada propriedade) e não pode ser editada . Deve-se notar que a coluna PropName não é confiável (imprecisa / inconsistente) e, portanto, o PropID deve ser usado para correlacionar as duas planilhas.

Gostaria de ter uma fórmula na coluna "Data de Recuperação de 100%" na planilha Resumo que informará a data em que a Saída total (cumulativa) atinge ou excede 100% da Entrada (ou seja, a data em que a investimento foi recuperado). Presumi que precisaria de uma fórmula para fazer o seguinte, embora possa estar errado:

1. Soma todos os dados de produção (retorno do investimento) de cada Propriedade, do mais antigo ao mais recente (o conjunto de dados já está classificado dessa maneira)

   ATÉ atingir 100% da Entrada / investimento original (mostrada na coluna Entrada total na planilha Resumo),

2. depois, retorna a data (localizada em um campo na planilha de conjunto de dados) quando a recuperação de 100% é atendida,

3. Se a recuperação de 100% não for atendida, retorne "TBD".

Por exemplo, o 100% RecoveryDate desejado para a propriedade 0764 (“Prop 1”) seria 6/7/2013 (da sexta linha de dados da planilha Data Set) porque 1.667000055+ 5.000999928 +1.667000055 + 3.334000111 + 5.000999928 ( das primeiras seis linhas da folha de conjunto de dados) = 16.670000077, que é maior que 13 (Entrada total para a propriedade 0764).

Todo e qualquer feedback construtivo é bem-vindo e muito apreciado!

Exemplo de planilha de resumo:

PropID   PropName   TotalInput   CurrentOutput  100%RecoveryDate  ProgressToRecovery
 0764     Prop 1          13         71,820                              189%
 0736     Prop 2      30,711        134,746                              439%
 1680     Prop 3      25,014         52,887                              211%
 4078     Prop 4      29,494         36,705                              124%
 5226     Prop 5      43,983         41,438                               94%
 6427     Prop 6      28,786         50,855                              177%
 6683     Prop 7      19,231         60,501                              315%
 6739     Prop 8      28,350         48,229                              170%
 9153     Prop 9      37,888         28,125                               74%
 8020     Prop 10     31,429         41,094                              131%

Exemplo de conjunto de dados:

PropID   PropName     Date          Output
 0764     Prop 1    6/1/2013      1.667000055
 0764     Prop 1    6/3/2013      5.000999928
 0764     Prop 1    6/7/2013      1.667000055
 0764     Prop 1    6/10/2013     3.334000111
 0736     Prop 2    6/19/2013   361
 0764     Prop 1    6/19/2013     5.000999928
 0764     Prop 1    6/22/2013     6.668000221
 0764     Prop 1    7/12/2013     3.334000111
 1680     Prop 3    7/17/2013   389
 0764     Prop 1    7/23/2013    10.00200081
 0736     Prop 2    8/2/2013    236
 4078     Prop 4    8/22/2013   236
 0764     Prop 1    8/25/2013     6.668000221
 0764     Prop 1    8/30/2013     3.334000111
 0764     Prop 1    8/31/2013     5.000999928
 0764     Prop 1    9/11/2013     1.667000055
 6427     Prop 6    9/15/2013  1018
 1680     Prop 3    9/16/2013   389
 0764     Prop 1    9/20/2013     6.668000221
 0764     Prop 1    9/22/2013    10.00200081
 0764     Prop 1    9/23/2013    10.00200081
 5226     Prop 5    9/23/2013   125

    

Answer 1

Crie uma folha fictícia. Vincule-o à planilha de conjunto de dados da seguinte forma:

• Clique na célula A1 e

  • digite ='Data Set'!A1 ou
  • digite = , clique na guia "Conjunto de dados" e clique na célula A1 nessa folha

  e digite Enter ou clique na marca de seleção à esquerda da barra de fórmulas.

• Clique na caixa de nome (à esquerda da barra de fórmulas) e digite um intervalo que cubra todas as linhas da planilha de conjunto de dados, incluindo (no mínimo) as colunas “PropID”, “Date” e “Output”. (Estas são as colunas A , C e D no exemplo, e você diz que tem 700K + linhas, então você pode digitar A1:D999999 .)
• Digite Enter . Para ilustração:
• Cliquenabarradefórmulas(quedevedizer='DataSet'!A1)edigiteCtrl+Enter.

Então,

• Insira=IF(SUMIFS($D$2:$D2,$A$2:$A2,$A2)>VLOOKUP($A2,Summary!A:C,3,FALSE),ROW(),"") na célula G2 e digite Enter .
• Insira =MIN(IF(($A$2:$A$999998=$A2), ($G$2:$G$999998), 999999)) na célula H2 e digite Ctrl + Deslocamento + Insira , tornando-se uma “fórmula de matriz”.
• Arraste-os para a linha 999999.

  Unfortunately, I can’t figure out how to get the Name Box trick to work for the array formula; you may just need to manually drag it.

• Ir para a folha Resumo.
• Na célula E2 (a primeira "100% RecoveryDate"), insira: =IF(ISERROR(VLOOKUP(A2,Dummy!A:H,8,FALSE)), "No Data!", IF(VLOOKUP(A2,Dummy!A:H,8,FALSE)=999999, "TBD", INDEX(Dummy!C:C,VLOOKUP(A2,Dummy!A:H,8,FALSE)))) .
• Formate a célula como uma data.
• Centralize-o, se quiser.
• Arraste-o até a última linha de dados na planilha Resumo.

Note: You will need to modify the above if your Data Set ever reaches 1000000 (one million) rows.  I hope the places are obvious.