How to create Harvey balls to show progress of work

Shile
Shile
edited 10/22/21 in Formulas and Functions

Hello,

Please I need help creating Harvey balls that will

show “Green” when work progress is FULL

show “Red” when work progress is NOT FULL and End Date is less than TODAY

show “Yellow” when work progress is less than FULL and NOT EMPTY

show “Gray” when work progress is EMPTY and End Date is BLANK 

Would also like the Harvey balls to roll up in the summary rows.


Thank you.



Best Answer

  • Parker Oxford
    Parker Oxford ✭✭✭✭✭
    edited 10/22/21 Answer ✓

    @Shile

    Ah sorry, I thought I tested all the cases, I've accounted for it in this formula (below). If any other case doesn't work please let me know.

    =IF(AND([work progress]@row = "", [End Date]@row = ""), "Gray", IF([work progress]@row = "Full", "Green", IF(AND([work progress]@row <> "Full", [End Date]@row < TODAY()), "Red", IF(AND([work progress]@row <> "Full", [work progress]@row <> "Empty"), "Yellow", IF(AND([work progress]@row <> "Full", [work progress]@row <> "Empty"), "Yellow", "")))))
    

Answers

  • Parker Oxford
    Parker Oxford ✭✭✭✭✭

    Formula below, I matched your column names so let me know if it doesn't immediately apply.

    =IF([work progress]@row = "Full", "Green", IF(AND([work progress]@row <> "Full", [End Date]@row < TODAY()), "Red", IF(AND([work progress]@row <> "Full", [work progress]@row <> "Empty"), "Yellow", IF(AND([work progress]@row <> "Full", [work progress]@row <> "Empty"), "Yellow", ""))))
    


  • Thank you for taking the time.

    Every other formula worked , except show “Gray” when work progress is EMPTY and End Date is BLANK. It gives Red Harvey ball instead.

    I highlighted my preferred outcome in green versus what the formula spills out, in the illustration below. 

    I hope you can help. Thanks



  • Parker Oxford
    Parker Oxford ✭✭✭✭✭
    edited 10/22/21 Answer ✓

    @Shile

    Ah sorry, I thought I tested all the cases, I've accounted for it in this formula (below). If any other case doesn't work please let me know.

    =IF(AND([work progress]@row = "", [End Date]@row = ""), "Gray", IF([work progress]@row = "Full", "Green", IF(AND([work progress]@row <> "Full", [End Date]@row < TODAY()), "Red", IF(AND([work progress]@row <> "Full", [work progress]@row <> "Empty"), "Yellow", IF(AND([work progress]@row <> "Full", [work progress]@row <> "Empty"), "Yellow", "")))))
    
  • Works perfect! Thank you.

    Only modification was including "Gray" in " " at the end. Nice!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!