Complex IF AND Formulas

KWright84

I'm trying to automate the RYG circles according to several columns of a Smartsheet grid for project management purposes. The logic is below (I've also attached a visualization of the logic):

 

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"

 

I know this will require nested IFs and nested ANDs, but I am really not advanced with Smartsheet formulas. I also don't know if I'm correctly annotating number ranges (i.e. between 31 and 100).

Can someone put me out of my misery on how to set up this complex formula?

Nic Larsen

Try this... I abbreviated the column names for my own sanity. 

=IF(PPLD@row < TODAY(), "Gray", IF(AND(PPLD@row >= TODAY(), DFPLD@row < 100), "Green", IF(AND(PPLD@row >= TODAY(), DFPLD@row > 100, Complete@row < 0.75), "Red", "Yellow")

KWright84

This worked:

=IF([Proposed Product Launch Date]1 < TODAY(), "Gray", IF(AND([Proposed Product Launch Date]1 >= TODAY(), [Deviation from Proposed Launch Date]1 < 100), "Green", IF(AND([Proposed Product Launch Date]1 >= TODAY(), [Deviation from Proposed Launch Date]1 > 31, [Deviation from Proposed Launch Date]1 < 100, Completion1 < 75), "Yellow", IF(AND([Proposed Product Launch Date]1 >= TODAY(), [Deviation from Proposed Launch Date]1 >= 100, Completion1 < 75), "Red"))))

 

But when I tried to correct the column name (from Proposed Product Launch Date to Anticipated Project Launch Date), I got an INVALID OPERATION. Any ideas?

Nic Larsen

Try this -

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

Mike Wilday

One concern I see in your formulas is that percentage complete is not indicated with a decimal. Smartsheets records 75% as .75 and 50% as .5 so you will need to consider that in your formulas above. Also, be really careful where those rules are placed within the formula. Only some of your rules are based on percentages. You may want to move those up in the hierarchy so that another rule doesn't fire before it (I am not determining that they will, just that you should be sure). 

I have had many occasions where a particular rule didn't fire because a rule before it was always true... the IF statement will look at each IF and determine if its true or not. When it finds one that is true, it will return it's THEN command, and stop looking at the rest of the formula. Sometimes its best to look at outliers like other statements that also require percentages sooner in the game.  

KWright84

The winner is:

 

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

 

The reason it didn't work initially is because I added the Anticipated Project Launch Date column (I was doodling around with these formulas on a separate sheet, rather than the real grid, in case I made an irreparable error), and I hadn't formatted it as a Date. It was only Text/Numbers, and so it didn't understand TODAY(). I also had the GREATER THAN and LESS THAN symbols inverted for the timing I wanted. Once I corrected those things, the formula worked. Thanks so much, everyone!

Mike Wilday

I would do a functional test of your code. I don't think your final few IF statements will ever fire. 

The Completion@row < 75 won't read correctly if your completion percentage is formatted using the toolbar's percentage button but perhaps you have it set up differently.