MS Project (Desktop) - Total % Complete of all Total Number of Active Tasks under a Summary Task

[Scheduler_SPP]

I am looking for what I believe is a complex formula.

What I am looking to achieve is the following (Example), this would be a new Custom Field:

I have a Summary Task with say 10 active sub tasks. If say three of those tasks were 100% complete (So 3/10 complete), the Summary Task would indicate 30% Complete.

Essentially, the formula is looking at the total number of sub tasks under a Summary Task, checking the % Complete Field for 100%, if a task is 100%, then add 1, add up all the "1's", then task the Total number of Active Tasks, do the calculation to establish what % of the total number of Active Tasks are complete and thereafter, display the final % Complete. I'm not sure if the formula can only apply to Summary Tasks or not, but if not, the formula establishes whether or not it's looking at a Summary Task or a Sub Task, if a Sub Task, then ignore and leave blank for instance.

I would like to ensure it is looking at the total amount of Active Tasks as there are cases where a bunch of tasks were setup and baselined, but there are delays that were not part of the original plan, thus a new task is entered between two tasks to show the effects of that delay, so Active Tasks within the formula is crucial.

The reason for this formula is to be able to add this Custom Field to my Baseline Cost Report to indicate the production levels and establish how far behind our project team currently is in each different month. All other % fields are based on duration. I may require some advice on how to accurately display this Production level % in the Excel report as well.

Any assistance and/or advice would be greatly appreciated. I have using MS Project for about 6 months, if I have missed something, please feel free to show me my mistake, this would certainly allow me to learn and gain a much better understanding of MSP.

[#2]

@Scheduler_SPP 

No, it's not a complex formula. If done using extra fields with a formula (e.g. customize Task Number1 field), the formula will apply to all tasks, not just the summary line but even that could be set up to only show at summary level. I say "if done using extra fields" because an alternate approach would be to do this using VBA.

You talk about Active Tasks and cases where additional tasks are inserted due to various things. Are those new tasks not part of the "active task" count? If so, what delineates the difference between Active Tasks and new tasks (e.g. new tasks don't have a baseline set?)?

How exactly do you intend to "add" this new data parameter to your baseline cost? And you mention a report in Excel. How did you plan to get that?

John

[#3]

@Scheduler_SPP 

Hello

You can try the following...

Insert two custom numeric fields and one text field into your task list and enter the formulas and configuration of each field, as shown in the images below, through: Menu > Project > Properties > Custom Fields.

Number1 counts the total number of active subtasks, using the formula (copy and paste): IIf([Active];1;0)

Number2 counts the total number of subtasks completed 100%, using the formula (copy and paste): IIf([% Complete]=100;1;0)

Text1 calculates and rounds the percentage of tasks completed, adding the "%" symbol, only for summary tasks (copy and paste): IIf([Summary];round(100*[Number2]/[Number1]) & "%";"")

N.- If you want you can hide the Number1 and Number2 columns, and rename Text1.

I hope you find it useful.

Ignacio

[#4]

Incredible,

I didn't imagine it would be possible to insert formulas into MSP, I'm learning more and more in this community, thank you very much for sharing all the knowledge.

[#5]

@John-project → Thank you so much for your response, it's much appreciated!

Yes, so a few sub tasks are set and baselined as the plan of action and costs attached to each, but during the course of a project, things never go to plan within the construction industry and extensive delays occur, so what we do is insert these delay tasks before the task that was meant to take place begins (but no baseline is set for these delays), this shows the effects of the delay further on in our project. Non the less, these delays would need to come to an end before the next task takes place. The problem I have seen is that the first task (10 sub tasks in total) is set to say 2 days, then we have a delay of say 30 days, if I attached and Actual Start and Actual Finish to both the first task and the delay task, I have seen the summary task show me I'm 88% complete which is incorrect (% Complete Field), this is why I am looking to achieve this custom field which I can later use in my Visual Reports in Excel.

I understand you can add Custom Fields to your Visual Reports via 'Edit Template', and after some playing around, I figured out how to find these items (As they never showed up in the Pivot Table Field list). Firstly, I would drill down on the Pivot Table to a certain level, then by right clicking the task (In the example below, Task 4) → hover over 'Show Properties in Report' → You can then see these Custom Fields and other useful fields to display. Unfortunately I haven't found a BI Tool for custom Excel Reports using MS Project Desktop, this would be ideal.

[#6]

@Ignacio_Martin → This is fantastic, this is EXCATLY what I am looking for. THANK YOU SO MUCH, I REALLY APPRECIATE IT!!!!!

Just an update, in your formula's (And I'm not sure if it was a Region setting from Windows), but the semicolons were giving an issue in each formula, so I changed them to comma's and all worked 100%! I have made these commas in pink (Couldn't highlight in this editor) below for anyone struggling in the future 🙂

1) 

Menu > Project > Properties > Custom Fields.

Number1 counts the total number of active subtasks, using the formula (copy and paste): IIf([Active],1,0)

2) Number2counts the total number of subtasks completed 100%, using the formula (copy and paste): IIf([% Complete]=100,1,0)

3) Text1 calculates and rounds the percentage of tasks completed, adding the "%" symbol, only for summary tasks (copy and paste): IIf([Summary],round(100*[Number2]/[Number1]) & "%","")

[#7]

Hello Scheduler_SPP
I'm glad I understood and helped you.
The list separator symbol, usually ";" or "," depends, as you point out, on the regional configuration in the software or application being used, and can be configured by the user according to their preferences in Control Panel > Windows Region.
In many countries, such as the United States or Mexico, the comma is used as a list separator. In other places, such as in several European countries, Spain in my case, or in most Latin American countries, the semicolon is used as a list separator.
I have also observed that sometimes it is the operating system itself that automatically changes it to the user's region.
Therefore, the formulas must be entered with the separator symbol "comma" or "semicolon" depending on each country.
Regards.
Ignacio

[#8]

@Ignacio_Martin this worked great. Thanks for putting this up. One thing I had to change though... it threw a syntax error on the [Active] flag field. It didn't recognize it but when I changed it to [Flag1] it worked. My ver of MS Project apparently doesn't have an Active column so I used Flag1 in the formula and it works as you designed! Not sure why I didn't have Active but no matter, it's good now. Thanks again!

[#9]

@harmonica_man290 

Certainly in the Standard desktop version, as well as in previous versions, the Active field is not available. Yes it is in the Professional version.
In any case you used a good alternative to the formula, and I am glad that the proposal was useful to you.

Ignacio

[#10]

One follow up question, now that I've used it for a day. When I reopen the plan after it's been saved it has converted all my calculated task %'s into #ERROR. All I have to do is select Calculate Project and the errors disappear and show the correct % amounts. Any idea why it opens with errors?

[#11]

Hello
Can you confirm that the automatic calculation option is set to On, in File > Options > Schedule > Calculation > Calculate project after each edit?
Ignacio

[#12]

@Ignacio_Martin Yes, it is. I am using MS Project Standard 2019. It may just be too old 🙂

[#13]

@Ignacio_Martin Hi Ignacio, thought you may be interested in a slight modification I added to your solution. In my case I didn't want any task that is not active to be included in the % calculation. The way it is currently set up it will include the [% Complete] in the calculation even if the [Active] flag is set to "No". I just added an additional test for whether the Active flag (or in my case Flag1) is on and that seems to do what I wanted.
First I updated the formula for the Number 2 field:

Then I updated the formula for the Text field that displays the Task %:

Just wanted to thank you again for posting this solution. It's really helping me a lot!

[#14 · Accepted solution]

To those who may be following this thread:
The "answer" to the calculation question is addressed in my response to this same question on the Microsoft Answers forum at:
https://answers.microsoft.com/en-us/msoffice/forum/all/why-does-ms-project-not-calculate-upon-openin...

John

[#15]

@harmonica_man290 → I experienced the same issue each time I reopened the project file. The way in which I fix this is to right-click on the filed and select Custom Fields. Then select the dropdown and select Number (If you are not there already). Then go into each custom number (Starting at 1) and select the Formula Button (To view the formula), then click on OK. Go through this for each of them. I can't remember exactly what formula needed this, but it resolves the ERROR. I hope this helps 🙂