2

I have a client that wants to add a "Percent Tasks Complete" column to the tasks sheet. For example, the summary task "Safety Tests" contains:

  • "SafetyTest1"
  • "SafetyTest2"
  • "SafetyTest3"

SafetyTest1 is 100% complete (in the "% Work Complete" column). The other two safety tests are 90% complete. The percentage of tasks complete column should show "33.3%" in the "Safety Tests" summary task under the "Percent Tasks Complete" column because 1 out of the 3 tasks under that summary task are 100% complete, and 1 out of 3 = 33.33%.

There does not seem to be any easy way of adding a column like this. I tried creating a custom field, and I can get it to say "1" for each complete task and "0" for each incomplete task, which I thought could be a step along the way.

How do I do the rollup to summary tasks?

1
  • First you'd have to define "% complete". What do you intend to happen when you are 100% complete on test 1 (which requires 5 minutes) and 50% complete on test 2 (which required 3 engineers and five days). What % complete does that represent?
    – MCW
    Nov 25, 2014 at 15:34

2 Answers 2

2

Try using "% Complete" rather than "% Work Complete", Project will calculate % Complete automatically for "SafetyTest" if you have "SafetyTest1", "SafetyTest2" and "SafetyTest3" as subordinate tasks (you just need to ensure that the three subordinate tasks are immediately below and indented to the right relative to the "SafetyTest" task).

Using the example you provide where SafetyTest1 is 100% complete and the other two are 90% complete, Project will calculate % Complete for SafetyTest as 93% if we assume that all subordinate tasks are the same duration.

2
  • Yes, but if I understand you correctly, this misses the point of the client's requirement. They don't want to see the percentage of WORK complete. They want to see the percentage of TASKS complete. Their thinking goes along these lines: A task is either complete or its not. (Ie. anything less than 100% needs to count the same as zero.) In the example I gave the result they want to see is 33.33%.
    – Andrew
    Nov 21, 2014 at 14:17
  • @user2136212 - If you work through the example but only fill in 0% complete or 100% complete for your tasks you will find that you will get the output that you want.
    – Doug B
    Nov 24, 2014 at 13:03
0

A colleague of mine came up with the following solution:

It is achieved by using formulas which adapt according to the row type on tasks sheet such as: IIf([Milestone],0,1) (which when rolled up creates a straightforwards count of the number of tasks)

Column "Number1" is set to the custom field expression: IIf([% Complete]=100,1,0) and that field has Rollup set to Sum. This creates a count of completed tasks.

Column "Number2" is set to the custom field expression: IIf([Milestone],0,1) and is also rolled up using Sum. This creates a count of the total number of tasks, either complete or incomplete.

Then the values provided by these fields are combined to give the required result by means of this calculated field expression: IIf([Summary],Format([Number1]/[Number2],"###%"),"")

Checking that it is Summary task line ensures that the percentage of completed tasks only gets filled in for Summary tasks, not for individual tasks where it doesn't make sense to ask.

The first two columns can be hidden if not needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.