Need help with formula/function to determine RAG Status

GeethikaVarsha Kantamneni

I am trying to put a formula to determine the RAG Status in the project plan . It has the start date , end date & dependencies enable.

Below is my requirement,

1. For computing the overall RAG status at a rollup/parent activity level, the status of only those activities that are in critical path to be considered .So is there any formula/function , to automatically identify the tasks on critical path and then determining the variance?? I know that the tasks on critical path can be highlited and filtered within smartsheet but is there any way to include as part of formula
2. If one of the activities is in Amber or RED status, then all the downstream dependent activities to be marked as Amber or RED

Thanks in advance for your help

😃



Bassam Khalil

Hi @GeethikaVarsha Kantamneni

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

GeethikaVarsha Kantamneni

Hi Bassam.M Khalil

Thanks for your reponse , attached a sample plan.

Sample plan.PNG

As in the image , i have RAG column to determine the Red , Amber , Green, status with the below formula.( Varinace <=0 - Green , Varince 0-10% - Yellow , Variance 10% Red)

=IF(Varaince@row / ([Plan Finish]@row - [Plan Start]@row) <= 0, "Green", IF(Varaince@row / ([Plan Finish]@row - [Plan Start]@row) < 0.1, "Yellow", "Red"))

1. So now for the roll up/parent task lets say Task A , i want the RAG Status for it to be computed based on its children tasks that are only on critcal path .
2. And if a subtask is identified as Amber or Red , i want all the downstream dependent activities to be marked as Amber or RED . Like in the sheet attached Sub task A2 is RED so Sub task A3 ,Sub Task B2 should also be RED

Jen Lange

@GeethikaVarsha Kantamneni , I'm not aware of a formula that checks whether a task is part of the critical path, so to incorporate that element in this solution, you would need to conduct some manual attributing.

1) Add a "Critical Path" checkbox column

2) Check the "Critical Path" checkbox where applicable to the task

3) Construct an IF statement in the parent RAG cell: =IF(AND(CHILDREN([Task Name]1)="Red",[Critical Path]@row=TRUE),"Red",IF(Varaince@row / ([Plan Finish]@row - [Plan Start]@row) <= 0, "Green", IF(Varaince@row / ([Plan Finish]@row - [Plan Start]@row) < 0.1, "Yellow", "Red","")))

4) Construct an IF statement in the children RAG cell: =IF(ANCESTORS()="Red","Red",IF(Varaince@row / ([Plan Finish]@row - [Plan Start]@row) <= 0, "Green", IF(Varaince@row / ([Plan Finish]@row - [Plan Start]@row) < 0.1, "Yellow", "Red","")))

These formulas might need to be tweaked, as I haven't trialed them in a sheet yet. Let me know if this puts you on the right path.

-Jen

If you appreciate my response, please recognize the effort with an "Insightful" or "Vote Up" selection. Thanks!