Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Automating Balls

edited 12/09/19 in Archived 2016 Posts

I have a formula that works for some of the functionality. I am having an issue getting the gray ball to work using this formula. 


=IF(AND(TODAY() - [End Date]4 > 0, [% Complete]4 < 1), "Red", IF([End Date]4 - TODAY() < 4, IF([% Complete]4 >= 0.9, "Green", IF([% Complete]4 > 0.5, "Yellow", "Red", IF(AND([End Date]4 <= TODAY(), [% Complete]4 = 1, "Gray"))))))


Can some one let me know where I am going wrong?






  • Kennedy Stomps

    Hi! I think the reworked formula below might work for you:


    =IF(AND(TODAY() - [End Date]4 > 0, [% Complete]4 < 1), "Red", IF(OR([End Date]4 - TODAY() < 4, [% Complete]4 >= 0.9), "Green", IF([% Complete]4 > 0.5, "Yellow", IF(AND([End Date]4 <= TODAY(), [% Complete]4 = 1, "Gray", "Red"))))


    If this doesn't accomplish what you're looking for, please give me an outline of what conditions should trigger which colors and I'll be happy to take another crack at this!

  • djm714
    edited 08/25/16

    What i am trying to accomplish is nested in two different columns. 


    1st - I am trying to get the balls to adjust based on % complete and date so that i can track whether a specific task will be at risk or late. I am also wanting it to turn grey if the % complete column is 100%. 


    2nd - I have been working to get the status column to change from not started to started based on % complete having any amount of of work done except 100%. If it is 100% complete then i want it to be marked as complete. 



  • Kennedy Stomps
    Kennedy Stomps Employee
    edited 08/26/16

    Hi DJM-- I think this formula will basically accomplish what you're looking for. The problem is that you have essentially the same criteria for the ball to run green as to turn grey, so it's very unlikely that the ball will ever turn grey based on the formula you provided earlier. I moved the "Grey" criteria in front of the "Green" criteria for this reason:


    =IF(AND(TODAY() - [End Date]6 > 0, [% Complete]6 < 1), "Red", IF(AND([End Date]6 <= TODAY(), [% Complete]6 = 1), "Gray", IF(OR([End Date]6 - TODAY() < 4, [% Complete]6 >= 0.9), "Green")))


    In response to your second point, this is much easier to set up. It would looks something like this:


    =IF([% Complete]1 = 1, "Complete", IF([% Complete]1 > 0, "Started", "Not Started"))


    Hope this helps!


This discussion has been closed.