Wednesday, May 09, 2012 7:39 AM
I have looked and looked and looked for how to write Microsoft Project RAG Status indicator formulas, but there is such conflicting posts, especially as this is a complex formula.
I want graphical indicators to represent the status of a task:
- Not Due to Start = White
- In progress and on track = Green
- Completed = Blue / Black
- At risk of being late (Less than 7 days to go and less than 50% completed) = Amber
- Late = Red
- Not Baselined = ‘?’
- Critical milestone / task indicator = tick of flag (may be in a difference formula
I have written so many formulas and they all get syntax errors or they do not represent what I want. Got any tips?
Wednesday, May 09, 2012 8:53 AM
IIf([Critical]="Yes","7",IIf([Baseline Finish]=4294967295,6,IIf([Baseline Finish]<[Finish],5,IIf([% Complete]=100,3,IIf([Start]<Now(),IIf([% Complete]>0,2,IIf([Finish]-Now()>7,IIf([% Complete]<50,4,1),1)))))))
Should you the number output as you set out in your list, which you can then convert to a graphic.
Thursday, May 10, 2012 8:48 AM
I have tried that and unfortunately it is not working as well as I would like. It seems that the order is not working.
I have made my criteria a little easier.
My current formula is:
Switch([% Complete]=100,'BLUE',[Finish]>[Baseline Finish]+1,'RED',[Finish]<=[Baseline Finish]-1,'GREEN',[Finish Variance]<=0,"White",[Finish]<=[Baseline Finish]+1,'AMBER')
However it is only showing completed tasks (blue) and not due to start tasks (white) when there is clearly late (red) and At risk (amber) tasks on there.
Thursday, May 10, 2012 9:01 AMModerator
I just want to clarify on Red and Amber scenario so that you can give a thought to it.
At risk of being late (Less than 7 days to go and less than 50% completed) = Amber - What if a task is just of 7 days or alike? Anything late means it has not reached to planned % Complete, reasons could be numerous. So, just wanted to understand the reason for "Amber" calculation.
Because a task of 17 days : say currently on 11th day shows 50% complete so it should be AMBER as per you, but looking logically its a Delayed task (should be at 65%) so nothing less then Red should go.
Thursday, May 10, 2012 9:07 AMHi Sapna,
There would have to be an exception in place. This might work better as a percentage.
So if a task has <20% of it's time left and has been <50% completed then it would be amber.
If you need further clarification let me know.
Thursday, May 10, 2012 9:54 AM
Had a quick bash at this, but to do it with a single field is going to be pretty convoluted with a beast of embedded if statements in there.
To make it easier to build (and maintain going forward), what I'd do is use flag fields for your indicators:
- Flag 1 - White
- Flag 2 - Green
- Flag 3 - Blue
- Flag 4 - Amber
- Flag 5 - Red
You'd give each flag field a formula to determine whether or not the conditions for that flag are true. The complexity that Sapna has identified could be factored into the formula for flag5
You'd then have a switch statement like you've got which assesses the flags in their order of precedence to determine what graphic should be used, which would need something like:
I've got 1=1 at the end as a catch all if none of the other conditions are true - but if you structure the flag formulas correctly this should hopefully not fire.
Hope this is helpful,
- Edited by Andrew Simpson Thursday, May 10, 2012 9:58 AM submitted before finished
Thursday, May 10, 2012 10:54 AMModerator
Adding to Andrew's comment that merging Red and Amber would lead to complex nested statements. We can work on it provide a fix criteria to differentiate between Red and Amber.
As of now making it short switch statement to track your -
- Marked As Answer by Sapna Shukla - Project MVPMVP, Moderator Thursday, June 14, 2012 6:23 AM
Tuesday, June 12, 2012 7:02 PM
Please see my recent blog entry for a pair of macros that create the sort of indicators you need:
- Proposed As Answer by Sapna Shukla - Project MVPMVP, Moderator Thursday, June 14, 2012 6:28 AM
Thursday, June 14, 2012 6:22 AMModerator
I'm closing the thread, re-open it if the suggested options doesn't help you.