Calcula o número médio de dias entre duas datas para campos exclusivos

Question

Preciso de ajuda com uma única fórmula para encontrar tarefas únicas e depois obter a média entre duas datas. Olhando para o exemplo abaixo:

Tarefa encerrada criada por Tarefa1 3/13/2018 1/1/2018
Tarefa1 3/13/2018 1/1/2018
Tarefa1 3/13/2018 1/1/2018
Task2 3/13/2018 2/1/2018
Task2 3/13/2018 2/1/2018
Task3 3/13/2018 01/03/2018 -

Eu preciso pegar Task1 e subtrair 3/13/2018 de 1/1/2018 para obter 71
Preciso ter Task2 e subtrair 3/13/2018 de 2/1/2018 para obter 40
Preciso ter Task3 e subtrair 3/13/2018 de 01/03/2018 para obter 12
Isso me dá uma resposta média de 41%

Existe uma fórmula que eu possa usar para obter essa resposta de 41?
eu tentei {= AVERAGE (B2: B6 - C2: C7)}, mas eu não sei como ter primeiro verificar se é único. Algo como {= Se A2: A7 for Único, MÉDIA (B2: B6 - C2: C7)}

Para complicar ainda mais, há momentos em que não haverá valores na coluna B (Fechado)

    

Answer 1

Você pode usar essa fórmula em uma coluna adjacente para contar as ocorrências de cada tarefa:

=COUNTIF(A$1:A1,A1)

Você precisa colocar isso na primeira linha e arrastá-lo para preenchê-lo na coluna. Isso faz com que ele transponha corretamente para produzir o resultado desejado. A fórmula conta quantas linhas até a linha atual contêm o mesmo valor:

Task1   1
Task1   2   
Task2   1
Task3   1
Task1   3   
Task4   1
Task2   2   
Task3   2

Em seguida, use outra fórmula em uma nova coluna para capturar a diferença de data, mas somente quando a contagem de ocorrências for igual a 1:

=IF(D1=1,B1-C1,"")

Task1   1/01/2011   1/01/2010   1   365
Task1   1/01/2011   1/01/2010   2   
Task2   4/01/2011   1/04/2010   1   278
Task3   6/01/2011   1/06/2010   1   219
Task1   1/01/2011   1/01/2010   3   
Task4   10/01/2011  1/10/2010   1   101
Task2   4/01/2011   1/04/2010   2   
Task3   6/01/2011   1/06/2010   2   

Em seguida, calcule a média da última coluna.

Você pode, é claro, combinar as duas fórmulas acima - eu simplesmente forneci as duas por causa da explicação.

Observe que as datas não afetam a contagem de ocorrências - apenas a primeira ocorrência terá o cálculo da data (portanto, você não permite intervalos de datas diferentes para linhas diferentes. Caso contrário, isso não funcionará - seu exemplo mostra duplicatas intervalos de datas).

Você precisa decidir o que deseja fazer com sua coluna vazia B. Por exemplo, você pode ignorar tarefas não fechadas na fórmula de diferença de datas da seguinte forma:

=IF(AND(NOT(ISBLANK(B4)),D4=1),B4-C4,"")