Calculating average time

Hi All.

I need a formula that would calculate average time of input of a product into the system.

When you take a look at the attachment, in the first column you will find employee name, second column contains the name of a product and the third column contains the time of input.
Now, I'd like to answer the question: how long, on average, does it take for an employee to prepare a product?

Note: I added colors to make it easier to understand how excel NEEDS TO CALCULATE it.

For the employee Tom we would need to:

1) Subtract the time in column C30 (20:20:07) from the time in column C23 (18:03:03), which would give us 02:17:04; It therefore took Tom 2 hrs, 52 min, and 2 sec to prepare the product_DDD.
2) To prepare product_CCC, it took Tom 00:52:05 (C23-C11),
3) To prepare product_BBB, it took Tom 02:50:58 (C11-C4),
4) To get an average, we would need to add the times: 02:17:04 + 00:52:05 + 02:50:58 and divide them by 3.

Q: How long, on average, does it take for an employee to prepare a product?
A: For Tom it's: 02:00:02

In this case, data needs to be calculated for Tom only. Later, I'd like to know the answer, how long it took Agnes to prepare the product.

Of course, each time I download the report, data (the highest time) is in a different row (all formulas in column D was input manually), so I need a universal formula to calculate the average time.

Hope what I wrote is clear.
Any help greatly appreciated.

If you put this formula in cell D2 and fill down, it will skip that top part and return the correct value you are looking for.

Formula:

Please Login or Register  to view this content.

Updated spreadsheet attached.

Brilliant!!! Thank you for your hard work. Excellent formulas.
Unfortunately, I use Excel 2003 at work and formulas such as: COUNTIFS and AVERAGEIF do not work. Is there any workaround?