Results 1 to 5 of 5

Thread: RAG formula based on completion - problem with summary tasks

  1. #1
    Join Date
    Mar 2011
    Posts
    2

    RAG formula based on completion - problem with summary tasks

    Hello,

    I'm currently using a formula allowing me to show trafiic lights (RAG) based on deviation from % complete compared to target. This is working well for the subtasks, but the highest level summary tasks seem to come out wrong, as the % which should be complete is based on the overall duration instead of the loading of the subtasks (e.g. steps with a lot of backended tasks will come out red, even though on track).

    The formula I'm using:
    IIf([% Complete]=100,10,IIf([Duration]=0,"-99999",IIf([Start]>[Status Date],"A",((ProjDateDiff([Start],[Status Date])/[Duration])*100)-[% Complete])))

    On top of that I'm using the visual elements:
    = 99999 -> no traffic light
    = A -> blue traffic light (not started yet)
    < 10 -> green traffic light (<10% behind schedule)
    < 20 -> amber traffic light (<20% behind schedule)
    > 20 -> red traffic light (>20% behind schedule)

    Could someone provide me with advise on how to "fix" this for the summary tasks?

    Thanks!
    Monique

  2. #2
    Join Date
    Dec 2007
    Posts
    2,297

    Re: RAG formula based on completion - problem with summary tasks

    Your formula will be quite long. Here is the beginning of this formula : it responds to the 1 and 2:

    IIf([% Complete]=100 And [Actual Finish]=[Baseline Finish],"1a",IIf([%
    Complete]=100 And [Actual Finish]<[Baseline Finish],"1b",IIf([%
    Complete]<100 And [Finish]<[Current Date] And ProjDateDiff([Baseline
    Finish],[Finish])/[Minutes Per Day]<=15,"2a",IIf([% Complete]<100 And
    [Finish]<[Current Date] And ProjDateDiff([Baseline
    Finish],[Finish])/[Minutes Per Day]>15,"2b"))))

    You will have to continue this logic for the other tests :
    Insert a Text(x) field : Insert / Column
    Right click on that field : Customize Fields
    Click the formula button
    Paste the above formula...
    then set the Graphical indicators as you want...

  3. #3
    Join Date
    Mar 2011
    Posts
    2

    Re: RAG formula based on completion - problem with summary tasks

    Dear einstein_007,

    Many thanks for your advise. Have tried inserting the formula, but get a "syntax error". Do you have any idea what cauld be causing this?

    Kind regards,
    Monique

  4. #4
    Join Date
    Dec 2007
    Posts
    1,742

    Re: RAG formula based on completion - problem with summary tasks

    IIf([Actual Start]=ProjDateValue("NA") And [Start]<[Status Date],"True","False")

    Returns true for unstarted tasks less than the status date, otherwise return false. You can then use a graphical indicator depending on T/F. You can also use some type of a statement to set RAG depending on the value you wish. We use the [SPI] (Schedule Performance Index) combined with a switch statement. This is placed into a text field and then the graphical indicator is determined by the value returned 1-green, 2-amber, 3-red. Substitute desired cutoff values.

  5. #5
    Join Date
    Apr 2011
    Posts
    5

    Re: RAG formula based on completion - problem with summary tasks

    Hello,

    I have a similar problem as Manique 01. I'm currently using a formula allowing me to show trafiic lights (RAG). My formual also works well for the subtasks, but the highest level summary tasks shows the RAG status for the overall duration date. What I need is the RAG Status on the summery task to reflect the the subtask with the shortest time left,the subtask that will expire first. e.g. if there are 6 subtasks under the summery level task, from which 5 will expire after 2 months and are on GREEN Status and 1 will expire within next 5 days and is AMBER, because it approuching deadline, I want the high level summery task to be AMBER and reflect the status of the task which is on AMBER (the one expiring in 5 days). The formula im using is pasted below.

    IIf([% Complete]<100,IIf([Finish]>Now()+7, "OK", IIf(Now()>[Finish],"Over","Nearly Expired")),"Complete")

    Could you help please? It will be very much appreciated.

    Many Thanks

    Adam

Similar Threads

  1. Formula to count Detail Tasks within a Summary Task
    By blacksheep in forum Microsoft Project
    Replies: 3
    Last Post: 20-03-2012, 08:11 PM
  2. How to "wrap text" for summary tasks when printing-project 2000
    By Antonio00 in forum Microsoft Project
    Replies: 3
    Last Post: 20-05-2011, 06:35 PM
  3. RAG formula - problem with summary tasks
    By Adam 1980 in forum Microsoft Project
    Replies: 1
    Last Post: 25-04-2011, 12:19 AM
  4. Windows Scheduled Tasks don't luanch if tasks not finished
    By Andy Candy in forum Windows Software
    Replies: 3
    Last Post: 21-08-2009, 11:47 AM
  5. Errors with EV calc's/summary tasks
    By Alison in forum Windows Software
    Replies: 3
    Last Post: 21-07-2009, 06:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •