Go Back   TechArena Community > Technical Support > Computer Help > Office Help > Microsoft Project
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



RAG formula based on completion - problem with summary tasks

Microsoft Project


Reply
 
Thread Tools Search this Thread
  #1  
Old 24-03-2011
Member
 
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

Reply With Quote
  #2  
Old 24-03-2011
Member
 
Join Date: Dec 2007
Posts: 2,241
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...
Reply With Quote
  #3  
Old 25-03-2011
Member
 
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
Reply With Quote
  #4  
Old 26-03-2011
Member
 
Join Date: Dec 2007
Posts: 1,714
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.
Reply With Quote
  #5  
Old 19-04-2011
Member
 
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
Reply With Quote
Reply

  TechArena Community > Technical Support > Computer Help > Office Help > Microsoft Project
Tags: ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


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


All times are GMT +5.5. The time now is 12:58 PM.