Automate Harvey Ball based on % of checked boxes

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

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

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

Answers