Formula and RYG balls

Kate P

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

 

 

 

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