Automating Balls

djm714

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?

 

Thanks,

 

Dan 

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

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

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!