Overall % Complete of all sub-deliverables with 'Development' in name

Scott Bales

Hello,

I am attempting to create 2 separate sheet summary fields that specify an overall % complete based on sub-parent task names. I would like to create an overall % complete for all 'PIR Facility Components' tasks as well as a combined overall % complete for 'Facility Application Development Changes' and 'Host/AN Program Changes'; basically providing overall % complete of requirements gathering (PIR Facility Components) as well as software development tasks (Facility Application.... and Host/AN Program changes). I'm not sure if it's relevant, but I am using the =avg(children()) formula to acquire my percentages and not the % complete of child rows + assigned duration method of determining % complete.

I can add "Development" to the 'Host/AN Program Changes' sub-parent tasks so "Development" exists in both development-specific tasks if that makes the formula easier. Any ideas?

I appreciate any insight. Thank you!

Paul Newcome

You'll need a column that brings the parent data onto every row.

=PARENT([Task Name]@row)

From there you would use something like this to get the overall %:

=AVG(COLLECT([% Complete]:[% Complete], [Helper Column]:[Helper Column], @cell = "Facility Application Development Changes"))

Paul Newcome

You would need to incorporate an OR function:

=AVG(COLLECT([% Complete]:[% Complete], [Helper Column]:[Helper Column], OR(@cell = "Facility Application Development Changes", @cell = "Host/AN Program Changes")))

Paul Newcome

You'll need a column that brings the parent data onto every row.

=PARENT([Task Name]@row)

From there you would use something like this to get the overall %:

=AVG(COLLECT([% Complete]:[% Complete], [Helper Column]:[Helper Column], @cell = "Facility Application Development Changes"))

Scott Bales

That worked, Paul. Thank you. Per my table above, could you please let me know how I would average both "Facility Application Development Changes" and "Host/AN Program Changes" deliverables?

I appreciate your time.

Paul Newcome

You would need to incorporate an OR function:

=AVG(COLLECT([% Complete]:[% Complete], [Helper Column]:[Helper Column], OR(@cell = "Facility Application Development Changes", @cell = "Host/AN Program Changes")))

Scott Bales

That did it. Thanks again Paul. Appreciate your time.

Paul Newcome

Happy to help. 👍️