Formula and RYG balls

08/28/18 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 :(

 

 

 

Comments

  • Mike WildayMike 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 NewcomePaul 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)

    thinkspi.com

  • Mike WildayMike 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 WilliamsJ. Craig Williams Top Contributor

    blush

Sign In or Register to comment.