Roll Up Harvey Balls
I am currently working on a tracking page where I've been asked to create a "Progress" column using Harvey Balls. The goal is to use empty, half and full to represent not started, in progress and complete by training module (children) and then overall. Below is one of the many formulas I've been working on.
=IF(AND(COUNTIF(CHILDREN(), "Half") = 0, COUNTIF(CHILDREN(), "Empty") = 0), "Full", "Empty")
(AND(COUNTIF(CHILDREN(), "Half") = 1, COUNTIF(CHILDREN(), "Full"= 0", "Half", "Empty")
Comments
-
Missing parenthesis in last countif (also quote on number)
Current:
COUNTIF(CHILDREN(), "Full"= 0"
Should be
COUNTIF(CHILDREN(), "Full")= 0
-
=IF(OR(COUNTIFS(CHILDREN(), "Half") > 0, AND(COUNTIFS(CHILDREN(), "Empty") > 0, COUNTIFS(CHILDREN(), "Full") > 0)), "Half", IF(COUNTIFS(CHILDREN(), "Full") = COUNT(CHILDREN()), "Full", "Empty"))
First it will look for any Half's or a combination of Full's and Empty's. If it finds either of those two things, it will show as Half/In Progress. If it does not find either of those, it will count how many are Full. If the count of full is the same as the count of the children (meaning all Full), then it will show as Full/Complete. If it isn't either of those two then it will show Empty/Not Started.
-
Thanks so much!
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!