Using RYG to determine health status of progress made

ajs14

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

Paul Newcome

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"))