Formula and RYG balls

edited 12/09/19

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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)

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!