Eu tenho uma planilha de duas colunas. A contém tempo de cama (o tempo que eu fui para a cama) e B contém tempo de despertar (o tempo que eu acordei). Como eu poderia calcular o tempo médio de dormir e também o tempo de despertar?
Por exemplo:
A = 23:45, 23:00, 12:15, 12:45, 12:00
B = 6:00 am, 5:00 am, 5:30 am, 7:00 am, 6:00 am
Além disso, como calcular o tempo total dormido entre linhas? A fórmula média típica no Excel é descartada mesmo se eu converter em formato de 24 horas. Qualquer idéia será muito bem vinda.
Se você incluir a data com a hora, basta subtrair e calcular a média como os números normais. A formatação do número, no entanto, é importante para os dados serem exibidos corretamente.
Por exemplo:
AfórmulanacélulaC2é=B2-A2eafórmulaparaasmédiasnacélulaA7é=AVERAGE(A2:A6)
AformataçãonuméricapersonalizadaparaColunaC:usah"hrs" mm "min" e Colunas AB usam m / d / aaaa h: mm AM / PM . As células A7 e B7 usam o formato de número padrão Tempo .
Para fazer isso com precisão, sem precisar adicionar datas:
ComoMOD , pois o tempo "envolve" às 12h / 24h, o que equivale a 1 no mundo da planilha. Para conseguir isso:
C3: =mod(B3-A3,1)
Depois de ter as durações, a média é simplesmente:
C2: =AVERAGE(C3:C7)
Para as médias dos tempos reais, você precisará usar ARRAYFORMULA se houver a possibilidade de que os horários em qualquer coluna precisem se "envolver" à meia-noite (ou seja, pode haver alguns valores em um lado e alguns do outro). Para isso temos:
A2: =ARRAYFORMULA(AVERAGE(if(A3:A7>0.5,A3:A7,A3:A7+1)))
A fórmula acima simplesmente pega cada valor no intervalo, verifica se é mais de 0,5 (meio-dia) e se não adiciona 1 para movê-lo para o dia seguinte, então calcula a média de todos os valores. O 0,5 assume que você não vai para a cama antes do meio-dia, mas se você tem um horário incomum, você pode obviamente mudar.
Para a média dos tempos de despertar, o acima não é provavelmente necessário para a maioria das pessoas, porque geralmente você acorda entre a meia-noite um dia e a meia-noite seguinte, então nenhum dos seus valores precisa "envolver-se". Dito isto:
B2: =AVERAGE(B3:B7)
Para fazer isso com precisão nas datas:
Adicionar datas realmente apenas mascara os problemas com o cálculo de médias, porque está usando a data e a hora para a média. Está calculando a hora em um dia específico que é a média de todos os valores de data e hora. Isso leva a dois problemas:
O que você está realmente tentando classificar é o TIMEVALUES , por isso, basta adicionar isso a cada um dos intervalos nas fórmulas acima:
A2: =ARRAYFORMULA(AVERAGE(if(TIMEVALUE(A3:A7)>0.5,TIMEVALUE(A3:A7),TIMEVALUE(A3:A7)+1)))
B2: =ARRAYFORMULA(AVERAGE(TIMEVALUE(B3:B7)))
O C2 não precisa mudar, pois ainda está lidando apenas com durações e não com datas.