Alert (Red, Yellow, Green) comparing Harvey Balls to Expected % complete
Hello. I received a great formula by someone in the community to help me set up a column that would automatically alert red, green, or yellow comparing Harvey Balls to our Expected % Complete but it is showing red if our Harvey ball is showing ahead of the Expected % Complete and it should show green if we are ahead of schedule. Any ideas?
=IF(ABS(IF(Progress@row = "Full", 1, IF(Progress@row = "Three Quarter", 0.75, IF(Progress@row = "Half", 0.5, IF(Progress@row = "Quarter", 0.25, 0))))  [Expected % Complete]@row) < 0.2, IF(ABS(IF(Progress@row = "Full", 1, IF(Progress@row = "Three Quarter", 0.75, IF(Progress@row = "Half", 0.5, IF(Progress@row = "Quarter", 0.25, 0))))  [Expected % Complete]@row) < 0.1, "Green", "Yellow"), "Red")
Thank you!
Answers

Hi Ashley!
This one is tricky, and I'm having a hard time deciphering it. Do you have an actual %complete column? Is that where the Harvey balls are getting their data? If so, you could set the RYG formula to calculate off of a comparison of the two % complete (expected and actual) columns, rather than using the Harvey balls.
For example, if I wanted to set it up so that:
Tasks where Actual % complete is equal to or greater than Expected % complete show Green
Tasks where Actual % complete is 110% less than Expected % complete show Yellow
Tasks where Actual % complete is 11 or more % less than Expected % complete show Red
It would look something like this:
=if(([expected % complete]  [actual % complete]) <=0, "Green",if(AND(([expected % complete]  [actual % complete]) >0, ([expected % complete]  [actual % complete]) <0.101), "Yellow","Red")
This should translate to
If the expected % is smaller than or equal to actual %, Green
If the expected % is 010% greater than actual %, Yellow
Otherwise, Red. (In other words, if expected % is 10.1%+ greater than actual %, red)
Hope this helps.
Best,
Heather

The Harvey balls column is my actual complete. Do I need to create another column with percentages for this formula to work?
Help Article Resources
Categories
Check out the Formula Handbook template!