Microsoft Project 2010 RAG Status Indicators

Microsoft Project 2010 RAG Status Indicators

• יום רביעי 09 מאי 2012 07:39

Hey Guys,

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:

1. Not Due to Start = White
2. In progress and on track = Green
3. Completed = Blue / Black
4. At risk of being late (Less than 7 days to go and less than 50% completed) = Amber
5. Late = Red
6. Not Baselined = ‘?’
7. 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

Hello Jack,

Try this:

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.

Thanks,

Andrew

• יום חמישי 10 מאי 2012 08:48

HI Andrew,

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.

• Not Due to Start = White
• In progress and on track = Green
• Completed = Blue
• At risk of being late (Less than 7 days to go and less than 50% completed) = Amber
• Late = Red

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.

Any ideas?

• יום חמישי 10 מאי 2012 09:01
מנחה דיון

Hi,

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.

Sapna S

• יום חמישי 10 מאי 2012 09:07

Hi 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

Hi Jack,

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:

﻿﻿﻿﻿Switch(
[flag3]=true, "Blue",
[flag5]=true, "Red",
﻿    [flag4]=true, "Amber",
[flag1]=true, "White",
[flag2]=true,  "Green",
1=1,"Error"
)

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.

Andrew﻿

• נערך על-ידי יום חמישי 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 -

• Not Due to Start = White
• In progress and on track = Green
• Completed = Blue
• Late = Red

Switch([Status]=3,"White",[Status]=0,"Blue",[Status]=1,"Green",[Status]=2,"Red")

Sapna S

• יום שלישי 12 יוני 2012 19:02

Please see my recent blog entry for a pair of macros that create the sort of indicators you need:

http://blogs.captechconsulting.com/blog/tim-fite/i-love-my-status-symbols

• יום חמישי 14 יוני 2012 06:22
מנחה דיון

Hi Jack,