Calculate average based on specific time or date

  • Thread starter Thread starter cpliu
  • Start date Start date
C

cpliu

I have data of travel time between 2 locations each day. There are
columns such as date, start time and end time in multiple columns.

How can I find the average of travel time on Monday, Tuesday, Wed etc
and the average of travel time if start time is between 7:00 to 7:30,
etc..?

Thanks for the help,
 
cpliu,

I have come up with a suggestion. Basically, I took your "average" two
ways. One way would be to look at the Total Average for all days over time,
and the second would be to look at the individual days themselves.

Basically, the start and stop time can be Differenced out (C2 - B2), with
that you can get an average travel time by taking the =AVERAGE(D2:D11)
formula. The second alternative (by Day) would require you to write a
SUMIF/COUNTIF statement. My example has the formula
=SUMIF(A2:A11,"Mon",D2:D11)/COUNTIF(A2:A11,"Mon") as per below.

Start Stop Diff
Mon 7:00 8:00 1:00 Total Avg
Tue 7:15 8:45 1:30 1:07
Wed 7:02 7:58 0:56
Thu 7:00 8:00 1:00 Mon Avg
Fri 7:15 8:45 1:30 0:58
Mon 7:02 7:58 0:56
Tue 7:00 8:00 1:00
Wed 7:15 8:45 1:30
Thu 7:02 7:58 0:56
Fri 7:00 8:00 1:00

I hope this answers your question.
 
Basically, the start and stop time can be Differenced out (C2 - B2), with
that you can get an average travel time by taking the =AVERAGE(D2:D11)
formula.  The second alternative (by Day) would require you to write a
SUMIF/COUNTIF statement.  My example has the formula
=SUMIF(A2:A11,"Mon",D2:D11)/COUNTIF(A2:A11,"Mon") as per below.
Click to expand...

Thank you, Thomas. That's great. I have all the date entered as month,
day, year like 08/21/2008. Is there a function that I can convert them
to Monday, Tuesday, etc in a different column?

Thanks,

cpliu
 
Hi

In column E
=TEXT(A1,"ddd")
Base your formulae on column I and D rather than A and D
=SUMIF(E2:E11,"Mon",D2:D11)/COUNTIF(E2:E11,"Mon")

Alternatively, if you didn't want to use another column
=SUMPRODUCT(--(Weekday(A2:A11,2)=1),D2:D11))/SUMPRODUCT(--(Weekday(A2:A11,2)=1))
where 1 = Monday, 2 = Tuesday etc.
 
Back
Top