RYGG formula does not work the same for all rows

Carol Rykovanova

Hi Smartsheeter's

Please help me figure out what the problem is. I am using the formula to show row's Status:

= IF (OR ([% Complete] @row = 1, [Start date] @row> TODAY ()), "Green", IF (Status @ row = "Not started", "Gray", IF (AND ([ Due date] @row> TODAY (), [% Complete] @row <0), "Gray", IF (Status @ row = "At risk", "Red", IF (AND ([Due date] @row = TODAY (), [% Complete] @row <0.5), "Red", IF ([% Complete] @row <0.75, "Yellow", IF (AND ([Due date] @row = TODAY (7), [ % Complete] @row <0.25), "Red", IF ([% Complete] @row <0.5, "Yellow", "Green"))))))

Lines 20-22 and 25-31 (Not Started) still do not respond to changes in the Status and % Complete columns. File attached

Copy of Document (3).pdf

Is there a mistake in the formula?

Thanks in advance

Kelly Moore

Hey @Carol Rykovanova

It appears rows 20-22 and 25-31 are green due to the OR statement in your first term

IF( [% Complete] @row = 1 OR Start Date]@row>TODAY() then Green

If you want 'Not Started' to be the deciding factor, move the Gray condition to the first term in your IF statement

=IF(Status@row = "Not started", "Gray", IF(OR([% Complete]@row = 1, [Start date]@row > TODAY()), "Green", IF(AND([Due date]@row > TODAY(), [% Complete]@row < 0), "Gray", IF(Status@row = "At risk", "Red", IF(AND([Due date]@row = TODAY(), [% Complete]@row < 0.5), "Red", IF([% Complete]@row < 0.75, "Yellow", IF(AND([Due date]@row = TODAY(7), [% Complete]@row < 0.25), "Red", IF([% Complete]@row < 0.5, "Yellow", "Green"))))))))

Kelly Moore

Hey @Carol Rykovanova

It appears rows 20-22 and 25-31 are green due to the OR statement in your first term

IF( [% Complete] @row = 1 OR Start Date]@row>TODAY() then Green

If you want 'Not Started' to be the deciding factor, move the Gray condition to the first term in your IF statement

=IF(Status@row = "Not started", "Gray", IF(OR([% Complete]@row = 1, [Start date]@row > TODAY()), "Green", IF(AND([Due date]@row > TODAY(), [% Complete]@row < 0), "Gray", IF(Status@row = "At risk", "Red", IF(AND([Due date]@row = TODAY(), [% Complete]@row < 0.5), "Red", IF([% Complete]@row < 0.75, "Yellow", IF(AND([Due date]@row = TODAY(7), [% Complete]@row < 0.25), "Red", IF([% Complete]@row < 0.5, "Yellow", "Green"))))))))

Carol Rykovanova

Hi KDM,

Thanks a lot. It's working now