Average of Networkdays where cell value is true

[EvanDef]

Hello,

I am trying to get an average number of Networkdays where specific cell values are true. If the project Status is 'ongoing' or 'overdue' in Sheet2, what is the average Networkdays of the open projects for each project lead for Column C in Sheet1?

Any help is appreciated, thanks!
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Sheet1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project Lead[/TD]
[TD]Count of Projects[/TD]
[TD]Avg Age of Projects[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[/TR]
[TR]
[TD]Status[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Project Lead[/TD]
[/TR]
[TR]
[TD]Ongoing[/TD]
[TD]1/2/14[/TD]
[TD][/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]Ongoing[/TD]
[TD]2/4/14[/TD]
[TD][/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD]Complete[/TD]
[TD]12/12/13[/TD]
[TD]3/1/14[/TD]
[TD]Joe[/TD]
[/TR]
[TR]
[TD]Overdue[/TD]
[TD]2/5/14[/TD]
[TD][/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]Complete[/TD]
[TD]1/15/14[/TD]
[TD]3/5/14[/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD]Overdue[/TD]
[TD]11/20/13[/TD]
[TD][/TD]
[TD]Joe[/TD]
[/TR]
[TR]
[TD]Ongoing[/TD]
[TD]2/13/14[/TD]
[TD][/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]Ongoing[/TD]
[TD]3/1/14[/TD]
[TD][/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD]Overdue[/TD]
[TD]12/5/13[/TD]
[TD][/TD]
[TD]Joe[/TD]
[/TR]
</tbody>[/TABLE]

 

[jarjarbingie]

=AVERAGE(IFERROR(NETWORKDAYS(IF((Sheet5!$D$2:$D$10=$A2)*((Sheet5!$A$2:$A$10="Ongoing")+(Sheet5!$A$2:$A$10="Overdue")),Sheet5!$B$2:$B$10,""),TODAY()),""))

press CTRL+SHIFT+ENTER

 

[EvanDef]

Hello Jarjar, This works with "Ongoing" and "Overdue" but I am getting #DIV/0! for PMs with only a few projects where the status is "Planned" or "Complete". One concern i have is some of the PMs with a mix of all 4 status and if the fomula is exluding the "Planned" or "Complete" status like it should.

Thoughts?

Thanks again for the help.

 

[jarjarbingie]

post deleted

 

[jarjarbingie]

=AVERAGE(IFERROR(NETWORKDAYS(IF((Sheet2!$D$2:$D$10=$A2)*((Sheet2!$A$2:$A$10="Ongoing")+(Sheet2!$A$2:$A$10="Overdue")),Sheet2!$B$2:$B$10,""),TODAY()),""),IFERROR(NETWORKDAYS(IF((Sheet2!$D$2:$D$10=$A2)*((Sheet2!$A$2:$A$10="Complete")+(Sheet2!$A$2:$A$10="Planned")),Sheet2!$B$2:$B$10,""),IF((Sheet2!$D$2:$D$10=$A2)*((Sheet2!$A$2:$A$10="Complete")+(Sheet2!$A$2:$A$10="Planned")),Sheet2!$C$2:$C$10,"")),""))

press CTRL+SHIFT+ENTER

 

[jarjarbingie]

must have read that wrong with my previous post, just disregard that..

the original formula I have provided only calculates the average of network days for Ongoing and Overdue projects, it excludes Complete and Planned.. naturally, you'll get an error if there are no Ongoing and Overdue projects for a particular person, just put an IFERROR around the formula:

=IFERROR(AVERAGE(IFERROR(NETWORKDAYS(IF((Sheet5!$D$2:$D$10=$A2)*((Sheet5!$A$2:$A$10="Ongoing")+(Sheet5!$A$2:$A$10="Overdue")),Sheet5!$B$2:$B$10,""),TODAY()),"")),"")

 

[EvanDef]

Jarjar,
Amazing it was that simple. I must have played with that first formula for 3 hours. Thank you very much for your help!
Salamat po!