# 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!

Tags:

• 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.

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

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!