Time-Weighted Average

[andyXL]

Hi

I've been tasked with analysing unload times from our warehouses, and after quite a lot of data extraction I need to address the average unload times. Initially I just ran an AVERAGE calculation across the column containing the information. This has turned out to be incorrect, as there are instances (one-offs) that need to be slightly filtered out. The guys work in pairs to unload, and instances that affect the times to give us an inaccurate figure are days when one guy is sick, thus almost doubling the time taken. So, to filter this we need a calculation that takes into account the time taken to unload, and the amount of occasions each unload time has come up. For instance:

Unload time taken in hh:mm

01:30
01:45
02:00
01:45
01:30
03:30

As the 3.5 hours unload time occurred with just one guy working on it, can we weight the average based on just this information? Excluding the 3.5 hours, the average is 01:42 (simple average calculation), with it in the calculation it jumps to 2 hours. There are other heavier spikes, such as damaged product being factored in, so some times spike at 5 hours+, again this throws the everyday realistic average out by quite a way, but because it's a one off it doesn't really need to be included.

My superior doesn't believe we should be removing the data manually, certainly as it's a report he wants to be able to run regularly and reasonably quickly.


Hope that makes any sense?

Thanks, Andy

[Ace_XL]

Have a look at the TRIMMEAN function