Complex IF AND Formulas

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?

Find more posts tagged with

Education

Formulas

Project Management

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…