Using RYG to determine health status of progress made
This is a question I am piggy backing on..
Automating a Roll up for RYG Symbols for Health — Smartsheet Community
I am also trying to use a similar formula but based on % Scheduled Elapsed, % Progress delayed and % completed. I'm using a template created but trying to make a new workspace that does pull from a different workspace as I don't have full access to the other one. I do not have the tolerance sheet it is referencing.
=IF([Start Date]@row <> "", IF(Children@row = 0, IF(AND([% Schedule Elapsed]@row = 0, [% Complete]@row = 0), "Gray", IF([% Schedule Elapsed]@row <= {Tolerances Range 1}, IF([Progress Delay]@row <= {Tolerances Range 2}, "Green", IF([Progress Delay]@row <= {Tolerances Range 3}, "Yellow", "Red")), IF([% Schedule Elapsed]@row <= {Tolerances Range 3}, IF([Progress Delay]@row <= {Tolerances Range 4}, "Green", IF([Progress Delay]@row <= {Tolerances Range 6}, "Yellow", "Red")), IF([% Schedule Elapsed]@row < 1, IF([Progress Delay]@row <= {Tolerances Range 7}, "Green", IF([Progress Delay]@row <= {Tolerances Range 8}, "Yellow", "Red")), IF([Progress Delay]@row > 0, "Red", "Green"))))), IF(SUM(CHILDREN([Health Roll-up Score]@row)) < 1, "Green", IF(AVG(CHILDREN([Health Roll-up Score]@row)) <= 1.66, "Red", IF(AVG(CHILDREN([Health Roll-up Score]@row)) <= 2.66, "Yellow", "Green")))), "")
Would someone be able to tell me how to use the tolerances referenced above? This is a premade formula so I am not even 100% sure if below is what I am trying to articulate.
This is the screen I am seeing:
This is what I want to achieve (i think):
If no progress delay = green
If progress delay 40-60% - yellow
if progress delay is 60-100 - red
--------
if % complete 0-40% = red
if % complete = 40-75 - yellow
if % complete 75-100 = green
Answers
-
Based on this:
If no progress delay = green
If progress delay 40-60% - yellow
if progress delay is 60-100 - red
--------
if % complete 0-40% = red
if % complete = 40-75 - yellow
if % complete 75-100 = green
You are going to want this:
=IF(OR([Progress Delay]@row<0.40, [% Complete]@row>= 0.75), "Green", IF(OR([Progress Delay]@row<0.60, [% Complete]@row>= 0.40), "Yellow", "Red"))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!