Alert (Red, Yellow, Green) comparing Harvey Balls to Expected % complete

Ashley Johnson

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!

Heather Duff

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 1-10% 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 0-10% greater than actual %, Yellow

Otherwise, Red. (In other words, if expected % is 10.1%+ greater than actual %, red)

Hope this helps.

Best,

Heather

Ashley Johnson

The Harvey balls column is my actual complete. Do I need to create another column with percentages for this formula to work?