Status Bubble Not Changing Based on % Complete Formula

edited 12/09/19 in Smartsheet Basics


I am currently trying to find a way for the Status bubbles to automatically color based on the % Complete column.  Reviewing other posts I found and have tried the formula below, but it only works correctly for Red and Grey.  When the percentage is above 51 or at 100 the color remains red.

=IF([% Complete]2 = 0, "Gray", IF(AND([% Complete]2 > 0, [% Complete]2 < 51), "Red", IF(AND([% Complete]2 > 51, [% Complete]2 < 96), "Yellow", IF([% Complete]2 = 100, "Green"))))

The % Complete column is set to "None" and is being calculated via the formula below:

=MIN(MAX((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row), 0), 1)

Thank you,


Status Bubbles.png