Complex IF AND Formulas

KWright84
KWright84 ✭✭
edited 12/09/19 in Formulas and Functions

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?

Slide1.JPG

Tags:
· Share on FacebookShare on Twitter

Comments

  • Nic Larsen
    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")

    · Share on FacebookShare on Twitter
  • KWright84
    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?

    · Share on FacebookShare on Twitter
  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    edited 01/22/19

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

    · Share on FacebookShare on Twitter
  • Mike Wilday
    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. :) 

    · Share on FacebookShare on Twitter
  • KWright84
    KWright84 ✭✭
    edited 01/22/19

    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!

    · Share on FacebookShare on Twitter
  • Mike Wilday
    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. 

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!