Status Bubble Not Changing Based on % Complete Formula

klward

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

Andrée Starå

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

 

Andrée Starå

To add: 

100% = 1

75% = 0.75

50% = 0.5

25% = 0.25

0% = 0

Paul Newcome

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

klward

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

Andrée Starå

Happy to help!

Haha!

Made me think of this!

Paul Newcome

Happy to help! 

 

And I know the feeling. Haha!