RAG formula based on completion - problem with summary tasks

[Monique_01]

Sponsored Links

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

[EINSTEIN_007]

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...

[Monique_01]

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

[JAMES_911]

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.

[Adam 1980]

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