Change Status Bubble Color Based on Percentage

David Nelms

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?

 

 

Brett Evans

You can do it like this...

https://app.smartsheet.com/b/publish?EQBCT=0383f2e8ef4b455990bca7cecc8526b9

 

The formulas are at the bottom,

David Nelms

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.

Evan Wilson

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

Laura

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

J. Craig Williams

Yes,

something like this

 

= if ( status-as-text-column&row = "Not Started", not-started-color, 

   if ( status-as-text-column&row = "In Progess", in-progress-color, 

   if ( status-as-text-column&row = "Complete", complete-color, "I did not find a match")

 

Craig

Patrick Galvin

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

J. Craig Williams

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 (non-started and complete will be correct (see #3)) but otherwise Red. That's probably OK.

 

Hope this helps.

Craig

Patrick Galvin

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?

 

 

 

Patrick Galvin

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

J. Craig Williams

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