Excel como obter uma média de coluna para linhas que atendem a vários critérios

Question

Gostaria de saber a média de dias entre as datas de abertura e fechamento de um item com uma data de fechamento em um determinado mês.

Assim, a partir do exemplo abaixo, em janeiro de 2013, os itens 2,5 e 6 foram fechados (o status Fechado pode ser RESOLVIDO ou CANCELADO), cada um aberto por 26, 9 e 6 dias respectivamente. Assim, dos empregos que têm data de fechamento em janeiro de 2013 (entre 01/01/2013 e 13/02/13), eles têm um tempo médio de abertura (entre a data de abertura e de fechamento) de 13,67 dias para 2dp.

Eu tentei algumas maneiras de fazer isso funcionar e acho que o problema que estou tendo é com a função MÉDIA.

Item_ID  Open_Date  Status      Close_Date

1        1/06/2012   RESOLVED   16/07/2012

2        20/12/2012  RESOLVED   16/01/2013

3        2/01/2013   IN PROGRESS

4        3/01/2013   CANCELLED  7/05/2013

5        3/01/2013   RESOLVED   12/01/2013

6        4/01/2013   RESOLVED   10/01/2013

7        1/02/2013   RESOLVED   15/02/2013

8        2/02/2013   OPEN

9        7/02/2013   CANCELLED  26/02/2013

    

Answer 1

Adicione uma coluna auxiliar onde você calcula a diferença entre aberto e fechado. Adicione outra coluna auxiliar onde você extrai o número do mês. Em seguida, você pode usar AverageIfs () para várias condições.

Se você não quiser usar colunas auxiliares, use uma fórmula Sumprodcut ().

Asfórmulasdascolunasauxiliares,começandonalinha2,foramcopiadas

E2=IF(Sheet1!$D2>0,Sheet1!$D2-Sheet1!$B2,"")

F2 =MONTH(Sheet1!$B2)

Na captura de tela, a célula H2 usa as colunas auxiliares com a fórmula

=AVERAGEIF(Sheet1!$F$2:$F$10,1,Sheet1!$E$2:$E$10)

A célula H4 não usa colunas auxiliares. A fórmula é

=SUMPRODUCT(--(MONTH(B2:B10)=1),--(D2:D10<>0),(D2:D10)-(B2:B10))/SUMPRODUCT(--(MONTH(B2:B10)=1),--(D2:D10<>0))

[Editar] Acabei de perceber que você deseja calcular a média com base no mês em que a solicitação foi encerrada. Por isso, você deseja alterar F2 para

=MONTH(Sheet1!$D2)

e / ou a fórmula em H4 para

=SUMPRODUCT(--(MONTH(D2:D10)=1),--(D2:D10<>0),(D2:D10)-(B2:B10))/SUMPRODUCT(--(MONTH(D2:D10)=1),--(D2:D10<>0))

O resultado é 14 para ambos os casos.