Help with RYGB Formula Logic

I can't seem to get this formula to work properly, and my eyes are crossed now. Would someone be willing to take a look to see what I'm doing wrong?

=IF(OR([% Complete]21 = 1, Complete21 = 1), "Green", IF(ISBLANK([Start Date]21), "", IF(AND([End Date]21 >= TODAY(), Complete21 <> 1), "Red", IF([Elapsed Duration]21 > [% Complete]21), "Yellow", IF(AND([End Date]21 - 5 <= TODAY(), [% Complete]21 <> 1), "Yellow", IF([Start Date]21 > TODAY(), "Blue", "Green")))))))

Here's what I'm trying to do (I welcome critique and criticism on this front as well):

If % Complete equal 100% then display Green
If Start Date is blank then display nothing
If End Date is greater than or equal to Today AND If % Complete does not equal 100%) then display Red
If Elapsed Duration is greater than % Complete then display Yellow
If End date minus five is less than or equal to today AND If % Complete is not 100% then Yellow
If Start date is greater than today then Blue
Else Green

Thank you!

Find more posts tagged with

Sheets

Formulas

Comments

Mike Wilday

Try this:

You had too many closing parenthesis. You can always delete all of the ending parenthesis and smartsheet will generate them for you. Otherwise, at a quick glance, everything else looks good.

John C

Hmmmm... I get "incorrect argument set". Where should I be looking to correct?

Mike Wilday

Found it... there was a closed parenthesis after teh Elapsed duration 21 > [% Complete]21.

=IF(OR([% Complete]21 = 1, Complete21 = 1), "Green", IF(ISBLANK([Start Date]21), "", IF(AND([End Date]21 >= TODAY(), Complete21 <> 1), "Red", IF([Elapsed Duration]21 > [% Complete]21, "Yellow", IF(AND([End Date]21 - 5 <= TODAY(), [% Complete]21 <> 1), "Yellow", IF([Start Date]21 > TODAY(), "Blue", "Green"))))))

Also... for ease of use and for copying and pasting to other rows... you can also replace the cell number with @row... It will always pull from the row number that your formula is on... like this.

=IF(OR([% Complete]@row = 1, Complete@row = 1), "Green", IF(ISBLANK([Start Date]@row), "", IF(AND([End Date]@row >= TODAY(), Complete@row <> 1), "Red", IF([Elapsed Duration]@row > [% Complete]21, "Yellow", IF(AND([End Date]@row- 5 <= TODAY(), [% Complete]@row <> 1), "Yellow", IF([Start Date]@row > TODAY(), "Blue", "Green"))))))

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…