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"))))))

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"))))

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...

=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"))))

Syed Muhafzal

Thanks @Paul Newcome

That nailed it perfectly!

Syed

Paul Newcome

Happy to help. 👍️