Status Bubble Not Changing Based on % Complete Formula

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

Status Bubbles.png

Find more posts tagged with

Comments

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!

switch

Paul Newcome

Happy to help!

And I know the feeling. Haha!

Sign in to join the conversation!

Quick Links

Trending discussions

Dashboard Adding Widgets Spontaneously
Howdy, I am working on developing a several dashboards for our team, and for whatever reason the widgets I place are multiplying. I have copied/pasted some, but other I have just added I am ending up with dashboards that look like this: When originally I only had the top four items. It has happened on several different…
Project Management with tracking costs
I am a construction project manager and built sheets that include actual costs for individual tasks, departments and overall project. I update the costs cells as invoices come in by manually calculating then entering in new amount. Ex: Plumbing: week 1 $300, week 2 $250, week 3 $150 = $700. I enter $700 in cell. I'm…
Showing "stacked" data / Statuses of items by swimlane
I have multiple items that each have their own statuses and that roll up to "workstreams" or swimlanes. On the dashboard, I would like to show the progress of the items by swimlane without breaking it out into separate widgets for each. This would ideally be a stacked bar where each bar represented the Items and was broken…