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

Options

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

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Options

    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
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!