# Status Bubble Not Changing Based on % Complete Formula

edited 12/09/19

Hello,

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,

Kim

• ✭✭✭✭✭✭
edited 10/29/19

Hi Kim,

% in Smartsheet is written as 1 for 100%, 0.51 for 51%.

Try that!

Did it work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

100% = 1

75% = 0.75

50% = 0.5

25% = 0.25

0% = 0

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

I also suggest double checking your criteria. Unless it is intentional, your current formula will be blank for 50%, 97%, 98%, and 99%.

You can also save yourself the trouble of setting high and low ranges for each color by keeping in mind that it will stop on the first true value.

So if you want some type of color for every percentage, you could also give something like this a try...

=IF([% Complete]2 = 0, "Gray", IF([% Complete]2 <= .5, "Red", IF([% Complete]2 <= .99, "Yellow", IF([% Complete]2 = 1, "Green"))))

• It never fails, I tried that before and it didn't work but this time it did! Just like calling IT after you have already tried restarting, and it works when they are watching..

Thank you!!

Kim

• ✭✭✭✭✭✭

Happy to help!

Haha!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

Happy to help!

And I know the feeling. Haha!