Summing product based off multiple dates and finding most recent

Could you upload some mock data for us to play about with? I find it easier to get to grips with when I'm looking at the same thing as you

ECN Tracking Spreadsheet 2012 process tab EXAMPLE.xlsx Here is an example of what we are doing
Thanks!

No Takers? I am more than happy to provide more information

Thanks For checking it out, I'll see if I can explain it.

First of all, these are mock dates so the numbers are a little wonky.


The "average time in queue" formula is this:

Please Login or Register  to view this content.

So this formula will sum the product of incomplete tasks (support1=0) and the where the previous department is complete (deng<>0 [contains anything]) take the date code for today (TODAY() and subtract the date code for previous dept (-deng) and divide by the number of ECNs in queue (/E5)

What I am attempting; is to find this average where there may be multiple departments working on a task at the same time, but one department may have to wait on all of them to be completed before they can accomplish their part of the task. So, when all the necessary dept are done it will appear in their queue and start counting days from there, but the depts will finish on different dates, so when the last dept finishes their task, that is when it will appear in their "queue"

I already have the "number ECNs in queue" formulas done to reflect the depts they wait on.


The "# ECNs in queue" looks like this when multiple depts are involved:

Please Login or Register  to view this content.

This formula simply finds how many tasks are incomplete (ecnco=0) and where the previous depts have completed their part-
(fab<>0)*(pfep<>0)*(_sqa1<>0)*(support1<>0)*(mfgeng<>0)


The "number of ecns overdue" formula looks like this:

Please Login or Register  to view this content.

Once again, this is summing the product where the task is incomplete (support=0) and where previous dept is complete (deng<>0) taking today's date code ($D$3) and subtracting the date when the previous dept completed (-deng) if it's greater than 7 days,

Hope that makes sense,
Thanks!

Any help would be GREATLY appreciated. I really would like to find out soon what direction I need to go with this.

Thanks for the response, I will point it out and see if I can add some helpful notes.

Now we are moving from linear sequence to a grouped flow[IF you look at the sheet, each department went from left to right in that order, now it won't be in that linear sequence], where one department may have to wait on multiple departments before it is in their "queue" and some departments may be working on it at the same time.

So I am struggling trying to create a formula that will reference the necessary [Prerequisite] depts by row, once the necessary depts have completed the task, finding the last date [which would be when it entered the new dept's queue] and calculating average from that.

I had tried:

Please Login or Register  to view this content.

and multiple variations of that...
My hope was that this would (if both "whse", and "buyer" were populated with date) find which was the last date entered by comparing and calculate average based off the last entered date

But then I realized I was comparing the named range as a whole and not by row.

I hope this helps, If you need more explanation I can certainly try, just let me know.

As far as the color scheme, we are remaining consistent with other documentation related to this process. It's hard to tone down yellow without making it look really ugly (believe me, I tried).

Thanks,

Does anyone have any ideas? I'd be happy to provide more info if required.