Excel: calcula o número médio de dias entre um intervalo de datas

Question

Eu gostaria de um pouco de ajuda para um cenário no qual estarei trabalhando com o Excel. Qualquer ajuda será muito apreciada.

Eu terei um intervalo cada vez maior de datas para itens específicos e preciso fazer uma planilha do Excel para determinar o número médio de dias entre eles por item. Basicamente, este é um exemplo simplificado de como planejo tabular os dados:

Item Code  | Date
A.ITEM     | January 15, 2017
B.ITEM     | January 16, 2017
A.ITEM     | January 22, 2017
C.ITEM     | January 25, 2017
A.ITEM     | January 31, 2017
C.ITEM     | February 2, 2017
B.ITEM     | February 12, 2017
B.ITEM     | February 24, 2017
C.ITEM     | March 7, 2017

Em seguida, criarei outra tabela que exibirá a duração média entre as datas por item. Eu imagino que vai ficar assim:

Item Code  | Average Life Span
A.ITEM     | 9 days
B.ITEM     | 20.5 days
C.ITEM     | 21.5 days

Qual fórmula eu precisaria para tornar a segunda tabela possível? Eu estive quebrando meu cérebro por um tempo agora e, porque eu não estou muito familiarizado com as funções Date no Excel, eu ainda não sei como. É mesmo possível?

Obrigado!

    

Answer 1

Note que a média das diferenças é justa (max-min) / contagem: (d1-d2) + (d2-d3) + (d3-d4) + ... = d1-dn

Com isso, você pode usar uma fórmula como (MAX(d1:dn)-MIN(d1:dn))/COUNT(d1:dn)

Isso colocaria todas as datas em um pote, portanto, você precisa filtrar adicionalmente pelos códigos - em vez de simplesmente MAX(d1:dn) , use MAX(IF(a1:an=code,d1:dn,0) como uma fórmula de matriz. Adicione ifs semelhantes para MIN e COUNT (ou use COUNTIF); observe que, para MIN, o valor else não pode ser 0, mas precisa ser algo muito grande.

    

Answer 2

Neste exemplo, o Código do item está na Coluna A, a Data é a Coluna B e, em seguida, eu adiciono novos dados. A linha 1 é títulos.

Primeiro, você precisará classificar seus dados toda vez que adicionar uma nova linha, com uma classificação de duas camadas:

• primeiro por código do item
• segunda a data

Em seguida, na coluna à direita de Data, adicione um cálculo de tempo entre e arraste / preencha:

=IF(A2=A1,B2-B1,"")

Depois, calcule a média dos tempos. Liste os códigos de item A, B, C em outro lugar - eu os coloquei na coluna F no meu teste. Ao lado de A, insira e arraste / preencha:

=AVERAGEIF($A$2:$A$6491,F2,$C$2:$C$6491)

Eu tenho:

• A: 8
• B: 19,5
• C: 20,5

(para A: 22 jan - 15 jan é 7 dias, 31 jan - 22 jan é 9 dias, a média é 8 dias)