Need help with formula/function to determine RAG Status


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
    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.

    PMP Certified

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Hi Bassam.M Khalil

    Thanks for your reponse , attached a sample plan.

    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
    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.


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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!