Formula and RYG balls

HELP!!!!!! How do i create a formula to use RYG balls in the status column with % Complete column no matter how many videos and threads i look through!!

I have a status column

I also have a % Complete column

I want to use balls to show how complete a project currently is

i want Red balls to show projects that are less than 50% complete (Just one of 3 i need) I hope someone can help

Find more posts tagged with

Forms

Formulas

Conditional Formatting

Comments

Mike Wilday

Hi Kate, this is really simple to complete if you understand how the percentage column works. There isn't super clear documentation about this but hopefully I can provide some clarity.

Smartsheets displays percentages as 25% and 100% but in the back end they are working off of decimals, .25 = 25% and 1 = 100%.

To calculate a red ball at 50% or less you have to use a decimal value.

Try putting this in the RYG column. You'll need to edit the % column title to the actual title of your column and adjust the percentages as you see fit.

=IF([% column title]@row < .5, "Red", IF([% column title]@row < .75, "Yellow", IF([% column title]@row <= 1, "Green")))

Paul Newcome

Mike: I personally only use green for 100%, yellow gets used for 99%-50%, and red is for anything less than 50%. (Just my personal preference). Either way you can shorten this up just a little bit by cutting out the third IF and saying if it isn't red or yellow, just make it green.

=IF([% column title]@row < .5, "Red", IF([% column title]@row < .75, "Yellow", "Green"))

Not to say your's is wrong. It works like a charm. Just my 2 cents and a different option to achieve the same results. (You can thank Craig for getting me hooked on shortening the formulas. Haha)

Mike Wilday

Yes, I would use the same, but was just throwing together an example real quick that they could append for their own uses. And YES thanks to Craig, I have been working on doing the same thing. "Down with long formulas forever."

J. Craig Williams

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.