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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!