Preparing a RAG report

To track status of a project or tasks, RAG (Red, Amber, Green) report is commonly used. Given below is a sample RAG report as on 04-Jun-09

The following logic is used for creating the report
 

For Open tasks: 

• Red: SCD – today()+1 is -ve
• Amber: SCD – today()+1 is 0
• Green: SCD – today()+1 is +ve

For Closed tasks:

• Red: SCD – ACD+1 is -ve
• Amber: SCD – ACD+1 is 0
• Green: SCD – ACD+1 is +ve

SCD = Scheduled Completion Date

ACD = Actual Completion Date

The following formulas are used
C2=CHOOSE(WEEKDAY(B2),”Sun”,”Mon”,”Tue”,”Wed”,”Thu”,”Fri”,”Sat”)

H2=IF(F2=”Open”,CHOOSE(2+SIGN(E2-TODAY()+1),”Red”,”Amber”,”Green”),CHOOSE(2+SIGN(E2-G2+1),”Red”,”Amber”,”Green”))

Column H is conditionally formatted to colour the cells –  Red, Amber or Green

Choose function Uses index_num to return a value from the list of value arguments has the syntax CHOOSE(index_num,value1,value2,…)

Sign function returns (0,+1,-1) depending on whether the input is 0,+ve or -ve

You could change the formula depending on the logic you want to use for RAG.

Have fun 🙂

Read Full Post »