Forum Discussion
Solved
MS Project (Desktop) - Total % Complete of all Total Number of Active Tasks under a Summary Task
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 t...
- 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-opening/8508dd45-b74d-4319-8301-7167d2858079
John
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
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!
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]) & "%","")
- 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.
IgnacioIgnacio_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!
- 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.
