Hi all, (also posted at http://www.excelforum.com/excel-gene...on-please.html)



The attached small sample is part of a huge batch of data I've been having to work through, and thanks to help I've received in these forums I think I can now see light at the end of the tunnel.

Overall I am tasked with producing is a series of averages, in essence a productivity exercise, I know you guys like detail, so here goes....

The attachment shows a sample of data which has already the start and end point of two sets of outcomes calculated. This produces the time, shown as App Time the task took to complete.

With help, I've now got the overall time all of these tasks took (ie App Times x Time) and now need to work out some averages.

The problem I have is that some tasks show as taking far longer than they actually took, as they were started and finished on different days or started before lunch etc. This pushes up the simple average I was hoping to use, and also makes a number of hard working people look inefficient, the total opposite I what I'm trying to achieve.

I have now hopefully set up some array calculations to work out the average, median and mode rather than using the simple total/number route. Problem is of course I'm not sure I've done it correctly, so need somebody to check my homework...

At the bottom of the attachment is my calculation area, which has the formula as I think it should be, along with what I am actually trying to do, so at the very least I hope the each formula is checking for each occurrence of "App Time" in cells in column A and then adding up each cell value to the right in column B.

What has confused me slightly is the difference between Average and Overall average, in Cells B359 & B360 which I would have thought would be the same.

I also guess that I do not get a MODE result as there are too many differing figures, so if anyone knows how to solve that issue I would be grateful.

Thank you reading!

Attached Files

SmallSample1.xls (227.0 KB, 9 views)

Cross post without links http://www.excelforum.com/excel-gene...on-please.html

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Yep, sorry got my bum smacked for this elsewhere...

Wasn't aware it was frowned upon, so have amended post.

Apologies all!