averageifs help for time on task by hour of day

[smithsm75]

Hello,
I am trying to average task time from Column E based on the hour of day from date and time in Column F. So for each hour of the day, I want an average task time. There are several months of data.

Thanks in advance,
Mike

E	F
0:41	6/11/2012 00:15:59
1:11 <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	6/11/2012 00:17:46 <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
0:13 <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	6/11/2012 00:30:47 <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
1:01 <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	6/11/2012 00:31:42 <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
1:49 <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	6/11/2012 00:33:09 <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
0:11 <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	6/11/2012 01:05:58 <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
0:11 <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	6/11/2012 01:15:53 <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
1:42 <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	6/11/2012 03:04:11 <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
2:37 <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	6/11/2012 03:06:57 <TBODY> </TBODY><COLGROUP><COL></COLGROUP>

<TBODY>
</TBODY>

 

[p45cal]

Do you have to use AVERAGEIF?
A pivot table will give you this in the fastest time.
The column F data in row section of the pivot and the average of the data in column E in the Data area of the pivot, then group the rows by Hours only. This is what I got with your data:

Excel Workbook
	E	F
6	duration	stamp
7	00:41	06/11/2012 00:15
8	01:11	06/11/2012 00:17
9	00:13	06/11/2012 00:30
10	01:01	06/11/2012 00:31
11	01:49	06/11/2012 00:33
12	00:11	06/11/2012 01:05
13	00:11	06/11/2012 01:15
14	01:42	06/11/2012 03:04
15	02:37	06/11/2012 03:06
16
17
18
19	Average of duration
20	stamp	Total
21	00	0:59
22	01	0:11
23	03	2:09
Sheet

The pivot table is at the bottom - you could also choose a pivot chart at the same time.

 

[smithsm75]

Thank you for the response. I am unfamiliar with pivot tables and I am working with several thousand rows.
--Mike

 

[p45cal]

I am working with several thousand rows

So much the better. A short 1 or 2 minute video. The data in the vid. is more than 9000 rows.
...
If you can't see clearly enough what's going on in the video, post back and I'll try and get a higher resolution version.

 

[smithsm75]

THANK YOU SO MUCH!!!!!! You are a ROCK STAR! It worked perfectly!!

Regards,
Mike