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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 348 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 282 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!