Como calcular um valor médio baseado em grupos duplicados?

Question

Eu tenho uma tabela que contém dados gravados em vários momentos. Os dados devem ter sido gravados em intervalos de 5 minutos, mas isso nem sempre foi o caso.

Portanto, para tentar limpar isso:

• Eu configurei uma coluna Hora normal que contém intervalos fixos de 5 minutos.
• A coluna Hora inserida contém os horários em que os dados foram registrados.

• A coluna Rounded Time contém o Entered Time arredondado para o intervalo de 5 minutos mais próximo (fórmula da célula C2 ):

  =MROUND(B2, "0:05")
  

• A coluna Contagem contém o número de ocorrências em que cada Tempo de arredondamento aparece (fórmula da célula F2 ):

  =COUNTIF($C$1:$C$19,C2)
  

• A coluna Duplicar / Única é simplesmente a coluna Contagem , mas representada com "Duplicado" ou "Exclusivo" (fórmula da célula G2 ):

  =IF(F2>1, "Duplicate", "Unique")
  

• A coluna Média de dados por tempo arredondado é o local em que estou tendo problemas, pois quero calcular a média dos dados para cada Horário padrão . Por exemplo. para 14/01/2014 12:40 , existem duas duplicatas, portanto, quero calcular a média dos dados da coluna Data e inserir isso na célula H2 . Em outras palavras, H2 conteria 23.18 . Como eu poderia conseguir isso?

Eu olhei possivelmente usando a combinação Index / Match mas não consigo entender.

    

Answer 1

Usando a função AVERAGEIFS , você pode informar ao Excel a média dos dados com base nos critérios que estão sendo atendidos.

Coloque esta fórmula em H2 e nas células subsequentes abaixo.

=AVERAGEIFS(E:E,C:C,C2)

Você receberá uma média dos dados da coluna E em cada linha da coluna H com base na média dos valores duplicados na coluna Rounded Time .

Nota: o formato Data / Hora pode não ser o mesmo, mas isso é irrelevante.