How to get result?

[Guest]

G'day

I am quit the novice at Excel at was wandering if somebody could help me? I need to get the average time in hours by type from the table below! Could anybody give me a hand

Type Received Date Closed Date Received Time Closed Tim
Normal 02-02-2004 02-02-2004 07:09:14 07:18:5
Normal 02-02-2004 02-02-2004 07:11:40 11:35:3
Normal 02-02-2004 02-02-2004 07:14:00 11:35:2
Medium 02-02-2004 05-02-2004 07:14:34 13:49:3
Low 02-02-2004 07-02-2004 07:28:37 13:43:2

Tia

Olli

 

[Frank Kabel]

Hi Ollie
assuming you want to average the solution time try the following:
1. Create a new column which stores the solution time per entry
(according to your example this may be column F). Enter in F2:
=(C2-B2)+(E2-D2)
2. format this cell with the custom format [hh]:mm
3. copy this formula down for all rows (F2:F6)
4. To get the average for your example enter
=AVERAGE(F2:F6)

 

[Guest]

Frank thanks! But then I still don't have it by type. And the table I am using has about 1100 entries. Is there a way like using the sumproduct formula? Would be very thankfull if you could solve this one.

 

[Frank Kabel]

Hi Ollie
One way (also using the new column E):
=SUMIF(A1:A1200,"Normal",E1:E1200)/COUNTIF(A1:A1200,"Normal")
change the ranges to your needs

Another way:
You might consider using pivot tables for this as it would create a
report in one step. The following sites will give you
some starting information:
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.cpearson.com/excel/pivots.htm

 

[Guest]

That did it! Thanks for the effort you've put into this!