Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Change Status Bubble Color Based on Percentage
Have tried using conditional formatting to achieve this with no luck. I have a column of % complete with percentages in increments of 5. I then have a seperate column of status that I am using a gray, red, yellow and green ball image. I'd like to for the color of the ball to change based on the percentage chosen (i.e. gray =0, green = 100, red = 5 to 50, yellow = 55 to 95). Any ideas?
Comments

You can do it like this...
https://app.smartsheet.com/b/publish?EQBCT=0383f2e8ef4b455990bca7cecc8526b9
The formulas are at the bottom,

Thanks. Wasn't sure if formulas would work, but now that I know they do, I can work on getting that part done, plus some other items that require similar programming.

I found that if I want to use whole numbers in the % column (25%, 50%, etc.), the formula should use decimals since that's how it will translate, once the % column is formatted.
e.g. (IF %Complete2 < .25, "Green")

Is there a way to use this same concept with Not Started, In Progress and Complete instead of the colors?

Yes,
something like this
= if ( statusastextcolumn&row = "Not Started", notstartedcolor,
if ( statusastextcolumn&row = "In Progess", inprogresscolor,
if ( statusastextcolumn&row = "Complete", completecolor, "I did not find a match")
Craig

can you add link to the Due date so that if due date is in next X days and % <> 100% then RED
currently have this:
=IF([% Complete]3 = 0, "Gray", IF(AND([% Complete]3 > 0, [% Complete]3 < .51), "Red", IF(AND([% Complete]3 > .50, [% Complete]3 < .96), "Yellow", IF([% Complete]3 = 1, "Green"))))

Patrick,
Here's my take on it:
=IF([% Complete]3 = 0, "Gray", IF([% Complete]3 = 1, "Green", IF(OR(AND([Due Date]3 < TODAY() + 5, [% Complete]3 < 1), [% Complete]3 <= 0.50), "Red", IF([% Complete]3 < 1, "Yellow"))))
I changed a few things:
1. These AND's are not needed
AND([% Complete]3 > 0, [% Complete]3 < .51)
if the IF's are nested properly. The first IF checked for =0. Unless you are concerned about negative percentages, the >0 is not needed.
Same with the next one in your original formula.
2. I changed < 0.51 to <= 0.5
In your formula, you would have shown Red at 50.5%, which I don't think you wanted.
3. I changed the order to check for Gray, then Green, then Red, and finally Yellow.
In this case, mostly a matter of preference. See also below on missing date.
4. I changed Yellow to less than 100%
In your original formula, 96.1% to 99.9% (rounded) did not get a result.
One thing neither of our formulas account for is a missing date (nonstarted and complete will be correct (see #3)) but otherwise Red. That's probably OK.
Hope this helps.
Craig

Hi
This is very usefukl for one project I wanted to report on so a huge thanks for that.
Where I think this needs a tweak is around the colours.
Gray = No started (Good for me)
Green = Completed (Good for me)
I need Yellow to indicate that this is progress
I need Red to indicate that either it's not started or less than 75%and the due Date is in xx days
I need Red to indicate % Complete < 1 and Due Date = < today()
Does that make sense...? Or how else do people track these things?

That is awesome  thank you so so much for assisting with this. would have taken me hours....!! Seem to work a treat.

It does.
I had a longer post, but a blue screen of death wiped it.
Here's the updated formula, rearranged again.
I added blank dates check for Gray result.
I'm using 5 as the X days. It shows up in two places
=IF([% Complete]6 = 1, "Green", IF(AND([% Complete]6 = 0, OR([Due Date]6 > TODAY() + 5, ISBLANK([Due Date]6))), "Gray", IF(OR([Due Date]6 > TODAY() + 5, AND([Due Date]6 > TODAY(), [% Complete]6 > 0.75)), "Yellow", "Red")))
Here's a picture of the results:
Hope that helps.
Craig