Help with RYGB Formula Logic

John C

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!

Mike Wilday

Try this: 

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

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