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,
- 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
- 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
😃
Answers
-
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.
bassam.khalil2009@gmail.com
☑️ 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"))
- 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 .
- 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
-
@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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!