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 Rollup Score]@row)) < 1, "Green", IF(AVG(CHILDREN([Health Rollup Score]@row)) <= 1.66, "Red", IF(AVG(CHILDREN([Health Rollup 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 4060%  yellow
if progress delay is 60100  red

if % complete 040% = red
if % complete = 4075  yellow
if % complete 75100 = green
Answers

Based on this:
If no progress delay = green
If progress delay 4060%  yellow
if progress delay is 60100  red

if % complete 040% = red
if % complete = 4075  yellow
if % complete 75100 = 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
Check out the Formula Handbook template!