none
Formula problem when predecessors are added RRS feed

  • Question

  • Hello!  I have been scouring the net for formulas to add a column in my project plan to add RAG visual status indicators.  I have one that works flawlessly on every task as long as there is not a predecessor to it.  Once I add a predecessor, it breaks.  By break I mean that the resulting response for the formula (in my case a color's name) spits out erroneous responses.  An example might be a task is clearly overdue and should be "red" but the response is "green" regardless of the % complete I put it.  It's like the calculation is "hung" once a predecessor is associated with the task.  The same thing happens with a number of formulas for RAGs that I find online.  I could really use some insight here!  Below is an example of the formula I use that breaks once those dependencies are added:

    IIf([Start]>Now(),"white",IIf([% Complete]=100,"blue",IIf((([Finish]-[Start])*(([% Complete]*1.1)/100))>([Current Date]-[Start]),"green",IIf((([Finish]-[Start])*(([% Complete]*1.1)/100))<([Current Date]-[Start]),"red"))))

    Please help!  I thank you in advance for any assistance here.

     -Ralph

    Friday, January 13, 2012 2:22 PM

Answers

  • GoreFish and others,

    The poster sent me his file. After analyzing the file this was my response back to him.

     

    First, the file contains links on summary lines. Although in this particular case there are no unexpected scheduling anomalies because of these links, it is not a recommended practice. With links on summary lines seemingly simple changes to the file structure can suddenly result in very strange unexpected results.
    Second, the original post mentioned a problem with the formula when predecessors were added. Task links have no impact on the formula in question so I'm not sure what that thinking was based upon.
    Third, I personally don't care that much for formulas of any complexity. They are difficult to develop, enter properly, and to troubleshoot if there is a problem. I prefer either breaking a complex formula into multiple simple formulas that are combined or using VBA. Breaking a custom field formula does take additional fields but is much easier to follow. That's basically what I did to troubleshoot this formula, I broke it into elemental parts and then examined each. Sometimes users insert additional parenthesis in a complex formula to isolate variables but it can also make reading the formula more difficult. I removed extraneous parenthesis in my version of the formula (see below).
    Fourth, the only issue I could find with the formula is the intent. The formula looks at each element in order of appearance in the syntax. The first statement that is true is the deciding factor. As written the first element says that any future task is "white". The second element says that any task that is complete is "blue". After that, any task this is at least 10% further along than it should be as of the current date is "green", everything else is "red". I suspect the issue is with the first two items. Even if a future task is complete ahead of time (i.e. 100%), per the formula it will show as "white", not "blue". Second, if a future task is started (i.e. % complete > 0), again the formula will show it as "white", not "green". The revised formula below contains changes that will achieve what I believe you want. That is, if a task is complete, regardless of whether it is a future task, it will show as "blue". Likewise, if a task has started, it will show as "green" even if it is a future task. Non-future tasks will follow the "green" and "red" criteria. For good practice, each "IIF" statement should have a true and false result. ProjectUser pointed that out in his response and I concur. Having a complete syntax can also aid in troubleshooting.
    Fifth, the idea of using the Status Date instead of the current date is a valid one brought up by Alexandre. As written the formula is based on a day-to-day progress metric, whereas using the status date relates to a more conventional measurement. It's basically a user's choice.
    Now for the more philosophical comment. Percent complete is a duration based metric and is really not a good way to view progress unless the schedule is made up entirely of tasks that follow the laws of nature or physics ("stuff" happens with the passage of time). It just isn't that good a measure for tasks where labor resources are involved. A much better measurement is % work complete or in some cases % physical complete. Work effort accomplishes things, the passage of time (i.e. duration) generally does not. To often management likes to see a quick and simple metric to gauge how well a project is progressing, hence the use of a red, yellow, green stoplight criteria, but it rarely gives a good indicator of how the project is really doing when the more complex aspect of useful work accomplished is considered. However, I only mention this as food for thought. Project progress measurement and metrics is a whole separate discussion.
    Suggested revised formula:
    IIf([Start]>Now(),"white",IIf([% Complete]=100,"blue",IIf((([Finish]-[Start])*(([% Complete]*1.1)/100))>([Current Date]-[Start]),"green",IIf((([Finish]-[Start])*(([% Complete]*1.1)/100))<([Current Date]-[Start]),"red","other"))))
    John

     

    Wrong formula. See my last post in this thread
    • Marked as answer by GoreFish Monday, January 16, 2012 4:21 PM
    • Edited by John - Project Monday, January 16, 2012 5:12 PM
    Saturday, January 14, 2012 9:33 PM

All replies

  • Ralph,

    In and of themselves predecessors won't change the formula you have. Rather, I suspect the predecessor(s) change one dates in the formula, probably to something you don't quite expect and then the formula responds accordingly. This is particularly true if you have predecessors on summary lines.

    In order for us to help you further we will need an example set of tasks from your file to help you figure out what is happening. If you want you can send me the file at the address below. Zip the file and I will ask some questions.

    John

    jensenljatatfastmaildotdotfm

    (remove obvious redundancies and the 7th character is an alpha)



    Friday, January 13, 2012 4:48 PM
  • False part is missing in the last IIF expression in the formula. That is, "red", False_Part ) ...

    Friday, January 13, 2012 6:50 PM
  • Sent you the sample file.  Thanks for your much needed help!
    Friday, January 13, 2012 6:58 PM
  • Tried just putting in that option (see below).  Didn't seem to have a visible effect.  Let me know if I mixed something up.

    IIf([Start]>Now(),"white",IIf([% Complete]=100,"blue",IIf((([Finish]-[Start])*(([% Complete]*1.1)/100))>([Current Date]-[Start]),"green",IIf((([Finish]-[Start])*(([% Complete]*1.1)/100))<([Current Date]-[Start]),"red","other"))))

    I am a inexperienced at Project formulas so bear with me if I stumble on seemingly easy things here.

    Friday, January 13, 2012 7:01 PM
  • Let's wait for John's post.
    Friday, January 13, 2012 7:23 PM
  • John,

    I would only like to highlight that creating a dependency on summary lines (tasks) is not a good practice according to Practice Standard for Scheduling (PMI). At this level of the WBS we found work packages and what we must sequence is "activities" (the action needed to deliver the workpackage).

    Best regards,


    Alexandre Paiva, PMP, MCTS, ITIL Project Manager +55 (21) 8887-3645 alexandre.paiva@gerentedeprojeto.net.br www.gerentedeprojeto.net.br
    Saturday, January 14, 2012 2:11 AM
  • Alexandre,

    Although I didn't specifically say so, my statement about links on summary lines causing things to happen in a Project file that users don't expect, indirectly implied that links on summary lines are a bad practice and should be avoided. Corefish did send me his file. I have not had a chance to review it. When I review the file and find the problem I will post back with what I found.

    John

    Saturday, January 14, 2012 2:42 AM
  • i know John. Just reinforcing the concepts because it's very common to find schedules in this way. Best Regards
    Alexandre Paiva, PMP, MCTS, ITIL Project Manager +55 (21) 8887-3645 alexandre.paiva@gerentedeprojeto.net.br www.gerentedeprojeto.net.br
    Saturday, January 14, 2012 3:06 AM
  • Ralph,

    It's always my concern about answering MS Project questions. For me it's impossible to use MS Project correctly if you will not have the right concepts and best practices of Project Management. 

    I'm telling this because in a well-designed dynamic schedule, each activity and milestone must have a predecessor and successor associated with them. The exception is the start and finish project milestone. (Practice Standard for Scheduling and PMBoK best practices)

    So If your schedule presents activities without predecessors and/or successors, it’s time to review it.

    Another advice: use Status Date instead of Current Date.

    An alternative is using the native fields like Status and Status Indicator instead, check the Aksel’s paper at http://www.msprojectblog.com/References/rag.pdf  (great paper)

    John is analysing your file and trying to find out what is wrong.
    Regards,


    Alexandre Paiva, PMP, MCTS, ITIL Project Manager +55 (21) 8887-3645 alexandre.paiva@gerentedeprojeto.net.br www.gerentedeprojeto.net.br
    Saturday, January 14, 2012 3:24 AM
  • Alexandre,

    I must disagree with your statement that, "each activity and milestone must have a predecessor and successor". All tasks must have a successor but not every task will have a predecessor. Case in point. A particular critical  resource, labor or machine, is not available until a specific date. The task(s) to which that resource is assigned could have a start-no-earlier constraint based on the resource's availability date. That task may or may not also have a predecessor. If the task does have a predecessor, resource availability could be controlled by a resource calendar instead of a constraint. The point being, not all tasks necessarily have a predecessor.

    John


    • Edited by John - Project Saturday, January 14, 2012 5:36 PM re-worded for clarity
    Saturday, January 14, 2012 5:12 PM
  • GoreFish and others,

    The poster sent me his file. After analyzing the file this was my response back to him.

     

    First, the file contains links on summary lines. Although in this particular case there are no unexpected scheduling anomalies because of these links, it is not a recommended practice. With links on summary lines seemingly simple changes to the file structure can suddenly result in very strange unexpected results.
    Second, the original post mentioned a problem with the formula when predecessors were added. Task links have no impact on the formula in question so I'm not sure what that thinking was based upon.
    Third, I personally don't care that much for formulas of any complexity. They are difficult to develop, enter properly, and to troubleshoot if there is a problem. I prefer either breaking a complex formula into multiple simple formulas that are combined or using VBA. Breaking a custom field formula does take additional fields but is much easier to follow. That's basically what I did to troubleshoot this formula, I broke it into elemental parts and then examined each. Sometimes users insert additional parenthesis in a complex formula to isolate variables but it can also make reading the formula more difficult. I removed extraneous parenthesis in my version of the formula (see below).
    Fourth, the only issue I could find with the formula is the intent. The formula looks at each element in order of appearance in the syntax. The first statement that is true is the deciding factor. As written the first element says that any future task is "white". The second element says that any task that is complete is "blue". After that, any task this is at least 10% further along than it should be as of the current date is "green", everything else is "red". I suspect the issue is with the first two items. Even if a future task is complete ahead of time (i.e. 100%), per the formula it will show as "white", not "blue". Second, if a future task is started (i.e. % complete > 0), again the formula will show it as "white", not "green". The revised formula below contains changes that will achieve what I believe you want. That is, if a task is complete, regardless of whether it is a future task, it will show as "blue". Likewise, if a task has started, it will show as "green" even if it is a future task. Non-future tasks will follow the "green" and "red" criteria. For good practice, each "IIF" statement should have a true and false result. ProjectUser pointed that out in his response and I concur. Having a complete syntax can also aid in troubleshooting.
    Fifth, the idea of using the Status Date instead of the current date is a valid one brought up by Alexandre. As written the formula is based on a day-to-day progress metric, whereas using the status date relates to a more conventional measurement. It's basically a user's choice.
    Now for the more philosophical comment. Percent complete is a duration based metric and is really not a good way to view progress unless the schedule is made up entirely of tasks that follow the laws of nature or physics ("stuff" happens with the passage of time). It just isn't that good a measure for tasks where labor resources are involved. A much better measurement is % work complete or in some cases % physical complete. Work effort accomplishes things, the passage of time (i.e. duration) generally does not. To often management likes to see a quick and simple metric to gauge how well a project is progressing, hence the use of a red, yellow, green stoplight criteria, but it rarely gives a good indicator of how the project is really doing when the more complex aspect of useful work accomplished is considered. However, I only mention this as food for thought. Project progress measurement and metrics is a whole separate discussion.
    Suggested revised formula:
    IIf([Start]>Now(),"white",IIf([% Complete]=100,"blue",IIf((([Finish]-[Start])*(([% Complete]*1.1)/100))>([Current Date]-[Start]),"green",IIf((([Finish]-[Start])*(([% Complete]*1.1)/100))<([Current Date]-[Start]),"red","other"))))
    John

     

    Wrong formula. See my last post in this thread
    • Marked as answer by GoreFish Monday, January 16, 2012 4:21 PM
    • Edited by John - Project Monday, January 16, 2012 5:12 PM
    Saturday, January 14, 2012 9:33 PM
  •  

    John,

    Thanks a lot for the chance to discuss the best practices of Project Management. The statement "each activity and milestone must have a predecessor and successor" is not mine.  It’s from PMBOK Guide 4<sup>th</sup> Edition.

    Let's go to your exemple and again I will revoke to the best practices from PMBOK Guide and the Time Management Knowledge Area. When you mention the resource’s availability during the planning phase, we are referring to Resource’s Calendar. Resource’s Calendar, according to PMBOK Guide, specifies when and how long identified project resources will be available during the project.

    The use of a constraint in this case (when the resource will be available) you mention is not a best practice too.

    So, if you are planning a project and creating a schedule based on best practices of Project Management (PMI), you must:

     1) Define Activities, which means activity list, activity attributes and milestone list.

     2) Create your Project Schedule Network diagram based on the activities defined, executing the Sequence Activities Process. In this diagram, again, every activity and milestone except the first and last are connected to at least one predecessor and one successor.

     3) Execute the 6.3 Process (Estimate activity Resources) to identify the types and quantitative of resources need (at this point we are not talking about staff assignments). The 9.2 Process will acquire the project team (staffs) and adjust the resource calendar.

    Doing that you have DYNAMIC SCHEDULE. Schedules like this are flexible; the tool knows how to update the other tasks automatically when preceding tasks are changed, or when resource availability changes.

    Create schedules with as many dependencies as needed and as few fixed dates (constraints or manually scheduled tasks in MS Project 2010) as possible. That’s the idea.

    Thanks once more for this rich discussion about Best Practices.

     


    Alexandre Paiva, PMP, MCTS, ITIL Project Manager +55 (21) 8887-3645 alexandre.paiva@gerentedeprojeto.net.br www.gerentedeprojeto.net.br
    Monday, January 16, 2012 12:29 AM
  • Alexandre,

    To every rule this is often an exception. However discussing this further in this thread is a divergence from the original question, so I'm going to just leave it as it is.

    John

    Monday, January 16, 2012 2:03 AM
  • Ok, John. No problem. Let's move on.

    My concern is always use the MS Project aligned to the best practices of Project Management. I really think Project Management Best Practices and MS Project should be hand in hand.

    That's why i got this opportunity to explain when i read the title of this thread "Formula problem when predecessors are added". Predecessor should be identified as an activity attribute at that point in time.

    Please, don't be mad at me. And you can count on me to everything you need.

    Best regards!

    Alex.


    Alexandre Paiva, PMP, MCTS, ITIL Project Manager +55 (21) 8887-3645 alexandre.paiva@gerentedeprojeto.net.br www.gerentedeprojeto.net.br
    Monday, January 16, 2012 11:46 AM
  • Thank you to all for the review of my issue (technical and otherwise!).  I think I have all the ammunition I need with the analysis above.  I am am indebted to each of you for taking the time to look over my challenge.  It has cleared my issue and made me a better PM.  As a footnote, I modified the formula slightly to fix an issue that was causing a problem when a task started and finished one same day (0 duration).  The correction did the trick!

    IIf([% Complete]=100,"blue",IIf([Start]>Now(),"white",IIf((([Finish]-[Start])*(([% Complete]*1.1)/100))>([Current Date]-[Start]),"green",IIf((([Finish]-[Start])*(([% Complete]*1.1)/100))<([Current Date]-[Start]),"red","other"))))

     

    Many thanks to each of you!!!

    Monday, January 16, 2012 4:21 PM
  • Ralph,

    You're welcome but I must give you the kudos. Although my detailed rhetoric was correct, the revised formula I posted above and in the e-mail I sent you is wrong. Apparently I copied the original formula and pasted it into my messages. Your revised formula will still show a future task as "white" even though it has started. I think it should show as "green", but that's depends on the user's intent. If you are happy with the formula you currently have, that's great.

    I do apologize for the misinformation, (wrong formula), and for the philosophical diversion, ( off-topic best practices discussion), in this thread. Herewith is the correct formula.

    IIf([% Complete]=100,"blue",IIf([Start]>now() And [% Complete]=0,"white",IIf(([Finish]-[Start])*[% Complete]*1.1/100>([Current Date]-[Start]),"green",IIf(([Finish]-[Start])*[% Complete]*1.1/100<([Current Date]-[Start]),"red","other"))))

    John



    Monday, January 16, 2012 5:09 PM