% Complete based on unrelated tasks?

Rachel Rundquist

My project plan has 1000+ lines built with parent and child tasks. I can see progress of each design item and task (ie: design item release has 20 tasks, formatted so the progress to release is the parent showing overall % complete of that design item). The project has 18 design items.

I've been asked to report on a mid-release state for all design items (ie: how are we progressing to all 18 design items up to each item's "task 10"). How can I best calculate % complete on this without having to recreate my sheet, or duplicate lines just for this request?

David Tutwiler

Off the top of my head, the best way to do this would be with 2 helper columns. Add one column to find out if it is a child task, and add another that determines which child task it is (first, second, etc). I don't know exactly how you have your hierarchy, but I used the following formulas to create the following counts (shown in an image below).

Helper 1

=COUNT(ANCESTORS())

Helper 2

=IF([Helper 1]@row = 1, [Helper 2]9 + 1)

Once you have that in place, then what you would need to do is run reports or point your metrics at anything that had the numbers 1-10 in the Helper 2 column.

Hope that helps.

Rachel Rundquist

Thanks David, that's an interesting solution I hadn't considered!

I'm not sure I follow how that will summarize the % complete on tasks 1-10 within the 18 design items, but I'll give it a shot. 🙂

Rachel

David Tutwiler

Instead of writing a formula to check all of the CHILDREN() under the parent design item, you will tell the formula or report to only gather the tasks that have a 1-10, giving you only the tasks you are looking for.