Automating RGY balls

jona28

Hello,

 

I am trying to automate the Red, Green, Yellow balls in my project plan but I can't seem to make the formula work. I want this to be based on 2 criteria, one being % complete and the other one on the end date. So I've tried something like =IF([% Complete]1 < 1, IF(TODAY(3 / 5 / 2019) - [End Date]1 > 0, "Red")) and I still have to add in this formula the options for yellow and green but I can't seem to make this work let alone a longer syntax. I realize that having this automated for what's already 100% and having a blue ball might be way too much since I'm a beginner but I'd like to figure out the RGY balls at least. So any help is appreciated! 

Thank you!

 

Nic Larsen

What are your other criteria for Yellow and Green. This will get you closer on Red.

=IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Red")

Nic Larsen

This is a bit more complete without knowing more:

=IF([% Complete]@row = 1, "Green", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Red", "Yellow"))

jona28

Hey Nic, 

Thank you so much for helping!!!

So I tried your formula and it did work. Now I'm adding the rest of the criteria and I got what I wanted. Thanks again!

=IF([% Complete]@row = 1, "Blue", IF(AND([% Complete]@row < 1, TODAY() - [End Date]@row > 0), "Red", IF(AND([% Complete]@row < 1, TODAY() - [End Date]@row < -3), "Green", IF(AND([% Complete]@row < 1, TODAY() - [End Date]@row < 0), "Yellow"))))

 

Paul Newcome

You actually do not need your AND statements to specify if the % Complete is less than 1 and [date criteria] because the formula would only move that far down the line if the % complete is less than 1. You can also get rid of one of the IF statements since there is the field for "else" or "otherwise". You can also simplify further by thinking of dates more along the lines of a year day as in January 1st is the first day of the year (1), February 1st is the thirty-second day of the year (32), etc.

 

So the first simplification of getting rid of the AND's looks like this...

=IF([% Complete]@row = 1, "Blue", IF(TODAY() - [End Date]@row > 0, "Red", IF(TODAY() - [End Date]@row < -3, "Green", IF(TODAY() - [End Date]@row < 0, "Yellow"))))

 

The second simplification brings it down to...

=IF([% Complete]@row = 1, "Blue", IF(TODAY() - [End Date]@row > 0, "Red", IF(TODAY() - [End Date]@row < -3, "Green", "Yellow")))

 

The third simplification (with a slight rearranging) would bring us to...

=IF([% Complete]@row = 1, "Blue", IF(TODAY() >= [End Date]@row, "Red", IF(TODAY(3) >= [End Date]@row, "Yellow", "Green")))

.

Not to say your current formula is wrong. Obviously it isn't since it is working for you. 

 

Just figured I would throw in a few pointers that may help if you get into more complex formulas.

jona28

Hello Paul,

Thank you for spending time on this! I am obviously new to the Smartsheet world. I did notice that the formula  I had was weighing in more the dates rather than % complete not sure if I am just imagining this or it's actually happening but I'll give your formula a try.

Paul Newcome

Happy to help. I was just giving a few pointers for more complex formulas to help keep them organized.

 

In the end, all that matters is what works for you. 

john.waddington102581

Hi Paul, you could you please expand upon each formula and explain what each is trying to accomplish?

Thanks!

Paul Newcome

John,

 

I am not sure what you mean. All three formulas accomplish the same goal, it's just three variations going from more complex to more simplified. All three formulas and the end goal are all explained within the original post and other comments throughout the thread.

 

Can you maybe be more specific about what it is you need an explanation on or need help with? I am just not sure of exactly what you are looking for.