יום רביעי 09 מאי 2012 07:39
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?
יום רביעי 09 מאי 2012 08:53
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.
יום חמישי 10 מאי 2012 08:48
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.
יום חמישי 10 מאי 2012 09:01מנחה דיון
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.
יום חמישי 10 מאי 2012 09:07Hi 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.
יום חמישי 10 מאי 2012 09:54
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,
- נערך על-ידי Andrew Simpson יום חמישי 10 מאי 2012 09:58 submitted before finished
יום חמישי 10 מאי 2012 10:54מנחה דיון
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 -
- סומן כתשובה על-ידי Sapna Shukla - Project MVPMVP, Moderator יום חמישי 14 יוני 2012 06:23
יום שלישי 12 יוני 2012 19:02
Please see my recent blog entry for a pair of macros that create the sort of indicators you need:
- הוצע כתשובה על-ידי Sapna Shukla - Project MVPMVP, Moderator יום חמישי 14 יוני 2012 06:28
יום חמישי 14 יוני 2012 06:22מנחה דיון
I'm closing the thread, re-open it if the suggested options doesn't help you.