Automate Harvey Ball based on % of checked boxes

Syed Muhafzal

Hi Smarties,

Having a bit of a problem with this challenge.

I would like to automate the [Progress] Harvey Ball's based on the % of checked [Completed] boxes in the children rows.

If 0-24% of boxes are checked, the harvey ball should be empty.

If 25-49% of boxes are checked, the harvey ball should be one quarter full.

If 50-74% of boxes are checked, the harvey ball should be half full.

If 75-99% of boxes are checked, the harvey ball should be three quarters full.

If 100% of boxes are checked, the harvey ball should be full.

If [Completed] is checked in the parent row, the harvey ball should be full, regardless of the children values.

I have created the below formula, but the Harvey Ball is either showing Full (when 1 checkbox is selected) or Empty (when nothing is selected) only on the parent level :(. I know I am close but yet it feels far!

=IF(PARENT(Completed@row) = 1, "Full", IF(COUNTIF(CHILDREN(), 1) <= 0.24, "Empty", IF(COUNTIF(CHILDREN(), 1) <= 0.49, "Quarter", IF(COUNTIF(CHILDREN(), 1) <= 0.74, "Half", IF(COUNTIF(CHILDREN(), 1) <= 0.99, "Three Quarter", IF(COUNTIF(CHILDREN(), 1) = 1, "Full"))))))

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Try something like this...

=IF(OR(Completed@row = 1, COUNTIFS(CHILDREN(Completed@row), 1) = COUNT(CHILDREN([Task Name]@row))), "Full", IF(COUNTIFS(CHILDREN(Completed@row), 1) / COUNT(CHILDREN([Task Name]@row))>= .75, "Three Quarters", IF(COUNTIFS(CHILDREN(Completed@row), 1) / COUNT(CHILDREN([Task Name]@row))>= .5, "Half", IF(COUNTIFS(CHILDREN(Completed@row), 1) / COUNT(CHILDREN([Task Name]@row))>= .25, "Quarter", "Empty"))))

All comments

Bassam Khalil

Hi @Syed Muhafzal

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Syed Muhafzal

Sure @Bassam Khalil here is the screenshot. The formula is written on the yellow cell.

Paul Newcome

Try something like this...

Syed Muhafzal

Thanks @Paul Newcome

That nailed it perfectly!

Syed

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.