Complex Formula for RYG Automation

KWright84 · 02/06/19

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] >=TODAYAND[Deviation from Proposed Launch Date] <100,"Green"

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

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

The final formula is:Smartsheet Overview

=IF([Anticipated Project Launch Date]@row>=TODAY(), "Gray", IF(AND([Anticipated Project Launch Date]@row@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,[email protected]< 75), "Yellow", IF(AND([Anticipated Project Launch Date]@row< TODAY(), [Deviation from Proposed Launch Date]@row>= 100,[email protected]< 75), "Red"))))

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

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

IF[Proposed Product Launch Date] >=TODAYAND[Deviation from Proposed Launch Date] >100AND[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@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,[email protected]< 75), OR([% Remaining Resources]@row< 5), "Yellow", IF(AND([Anticipated Project Launch Date]@row< TODAY(), [Deviation from Proposed Launch Date]@row>= 100,[email protected]< 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 · 02/06/19

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 showcondition, value_if_true, value_if_false):

IF(

AND([Anticipated Project Launch Date]@row< TODAY(), [Deviation from Proposed Launch Date]@row>= 100,[email protected]< 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,[email protected]< 75),

[% Remaining Resources]@row< 0),

"Red",value_if_false)

wherevalue_if_falsecan 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 · 02/07/19

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@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@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@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 · 02/07/19

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 · 02/07/19

Congratulations! That must be a relief.

另一个建议我必须让它有点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"

))))

Complex Formula for RYG Automation

Comments

Help Article Resources

Categories