Complex Formula for RYG Automation

KWright84

I automated the RYG circles according to several columns of a Smartsheet grid for project management purposes. The logic is below:

IF [Proposed Product Launch Date] <TODAY, "Gray"

IF [Proposed Product Launch Date] >=TODAY AND [Deviation from Proposed Launch Date] <100, "Green"

IF [Proposed Product Launch Date] >=TODAY AND [Deviation from Proposed Launch Date]= 31 - 100 AND [Completion] <75, "Yellow"

IF [Proposed Product Launch Date] >=TODAY AND [Deviation from Proposed Launch Date] >100 AND [Completion] <75, "Red"

The final formula is:Smartsheet Overview

=IF([Anticipated Project Launch Date]@row >=TODAY(), "Gray", IF(AND([Anticipated Project Launch Date]@row <TODAY(), [Deviation from Proposed Launch Date]@row < 100), "Green", IF(AND([Anticipated Project Launch Date]@row < TODAY(), [Deviation from Proposed Launch Date]@row > 31, [Deviation from Proposed Launch Date]@row < 100, Completion@row < 75), "Yellow", IF(AND([Anticipated Project Launch Date]@row < TODAY(), [Deviation from Proposed Launch Date]@row >= 100, Completion@row < 75), "Red"))))

 

But NOW, I would like to add ANOTHER option for "Red" and "Yellow".

IF [Proposed Product Launch Date] >=TODAY AND [Deviation from Proposed Launch Date]= 31 - 100 AND [Completion] <75, OR [% Resources Remaining] < 5, "Yellow"

IF [Proposed Product Launch Date] >=TODAY AND [Deviation from Proposed Launch Date] >100 AND [Completion] <75, OR [% Resources Remaining] <0, "Red"

 

I've tried the formula below, but it's not working.

=IF([Anticipated Project Launch Date]@row >=TODAY(), "Gray", IF(AND([Anticipated Project Launch Date]@row <TODAY(), [Deviation from Proposed Launch Date]@row < 100), "Green", IF(AND([Anticipated Project Launch Date]@row < TODAY(), [Deviation from Proposed Launch Date]@row > 31, [Deviation from Proposed Launch Date]@row < 100, Completion@row < 75), OR([% Remaining Resources]@row < 5), "Yellow", IF(AND([Anticipated Project Launch Date]@row < TODAY(), [Deviation from Proposed Launch Date]@row >= 100, Completion@row < 75), OR([% Remaining Resources]@row < 0), "Red"))))

 

Any idea where I'm going wrong? The row with a negative percent in "% Remaining Resources" is  coming back as INCORRECT ARGUMENT SET.

Andrew Stewart

It is tricky getting these compound formula to be syntactically correct. I find that building them up one step at a time by replacing the value_if_false with the next if statement (which can be tested in a difference cell by itself) makes it easier to track down the culprit. This is more or less what you are doing, anyway. The last If statement is (spaced out to show condition, value_if_true, value_if_false):

IF(

AND([Anticipated Project Launch Date]@row < TODAY(), [Deviation from Proposed Launch Date]@row >= 100, Completion@row < 75),

OR([% Remaining Resources]@row < 0),

"Red")

There is an OR function as the value_if_true, with only one parameter (it needs at least two). In fact, OR works just like AND, this part needs to be re-written as 

IF(

OR(

AND([Anticipated Project Launch Date]@row < TODAY(), [Deviation from Proposed Launch Date]@row >= 100, Completion@row < 75),

[% Remaining Resources]@row < 0),

"Red", value_if_false)

 

where value_if_false can be replaced by the rest of the logic required (but watch out, there is another OR with only one parameter earlier in the formula).

Hope this makes sense!

KWright84

Hi Andrew,

 

I'm no longer getting the INCORRECT ARGUMENT ERROR, but now I can't seem to get the order right. No matter how I set it up, one of the colors doesn't work.

 

=IF([Anticipated Project Launch Date]@row >=TODAY(), "Gray", IF(OR(AND([Anticipated Project Launch Date]@row < TODAY(), [Deviation from Proposed Launch Date]@row >= 100, [Completion]@row < 75), [% Remaining Resources]@row < 0), "Red", IF(OR(AND([Anticipated Project Launch Date]@row < TODAY(), [Deviation from Proposed Launch Date]@row > 31, [Deviation from Proposed Launch Date]@row < 100, [Completion]@row < 75), [% Remaining Resources]@row < 5), "Yellow", IF(AND([Anticipated Project Launch Date]@row <TODAY(), [Deviation from Proposed Launch Date]@row < 100), "Green"))))

 

GREEN DOESN'T WORK.

 

=IF([Anticipated Project Launch Date]@row >=TODAY(), "Gray", IF(OR(AND([Anticipated Project Launch Date]@row < TODAY(), [Deviation from Proposed Launch Date]@row >= 100, [Completion]@row < 75), [% Remaining Resources]@row < 0), "Red", IF(AND([Anticipated Project Launch Date]@row <TODAY(), [Deviation from Proposed Launch Date]@row < 100), "Green", IF(OR(AND([Anticipated Project Launch Date]@row < TODAY(), [Deviation from Proposed Launch Date]@row > 31, [Deviation from Proposed Launch Date]@row < 100, [Completion]@row < 75), [% Remaining Resources]@row < 5), "Yellow"))))

 

YELLOW DOESN'T WORK.

 

=IF([Anticipated Project Launch Date]@row >=TODAY(), "Gray", IF(AND([Anticipated Project Launch Date]@row <TODAY(), [Deviation from Proposed Launch Date]@row < 100), "Green", IF(OR(AND([Anticipated Project Launch Date]@row < TODAY(), [Deviation from Proposed Launch Date]@row > 31, [Deviation from Proposed Launch Date]@row < 100, [Completion]@row < 75), [% Remaining Resources]@row < 5), "Yellow", IF(OR(AND([Anticipated Project Launch Date]@row < TODAY(), [Deviation from Proposed Launch Date]@row >= 100, [Completion]@row < 75), [% Remaining Resources]@row < 0), "Red"))))

 

RED DOESN'T WORK.

 

I've broken it down how you suggested. I don't see how else I could make this work. I know there is a way to do this, as this sort of formula is crucial for automating RYG for Project Management purposes (i.e. a color to align with whether the project has started (gray), whether it's on time and on budget (green), behind OR not much budget remaining (yellow), or very behind OR overbudget (red).

KWright84

FYI, I tweaked the criteria and fixed the formula, if anyone wants to use something similar.

 

=IF([Anticipated Project Launch Date]@row >TODAY(), "Gray", IF(OR(AND([Anticipated Project Launch Date]@row <=TODAY(), [Deviation from Proposed Launch Date]@row >= 100, [Completion]@row < .5), [% Remaining Resources]@row < 0), "Red", IF(AND([Anticipated Project Launch Date]@row <=TODAY(), [Deviation from Proposed Launch Date]@row < 31, [% Remaining Resources]@row >= .05), "Green", "Yellow")))

Andrew Stewart

Congratulations! That must be a relief. 

Another suggestion I have to make it a little easier to follow the logic and therefore reduce the chance of error is to avoid multiples of the same test, and to write it out with line breaks and indentation that you can remove afterwards so it is easier to match up the brackets.

For instance, the first clause tests [Anticipated Project Launch Date]@row >TODAY(), and stops processing with Gray if it is true. So there is no need to test for the opposite in the value_if_false.

=IF([Anticipated Project Launch Date]@row >TODAY(), "Gray", 

 IF(OR(AND([Deviation from Proposed Launch Date]@row >= 100, [Completion]@row < .5), [% Remaining Resources]@row < 0), "Red", 

 IF(AND([Deviation from Proposed Launch Date]@row < 31, [% Remaining Resources]@row >= .05), "Green", 

     "Yellow"

    )))

 

This is not formatting neatly on my screen, hope it is legible to you.

Finally, I like to eliminate mixes of AND and OR in the same clause if possible, it is so easy to put the brackets in the wrong place and end up with an unintended result. So I would write it as follows, with two unrelated reasons for being red:

=IF([Anticipated Project Launch Date]@row >TODAY(), "Gray", 

 IF(AND([Deviation from Proposed Launch Date]@row >= 100, [Completion]@row < .5), "Red", 

 IF([% Remaining Resources]@row < 0, "Red", 

 IF(AND([Deviation from Proposed Launch Date]@row < 31, [% Remaining Resources]@row >= .05), "Green", 

     "Yellow"

    ))))