How to create Harvey balls to show progress of work
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

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

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

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
Categories
Check out the Formula Handbook template!