Harvey Balls Using Formulas & Multiple Columns

Sam Clapp

Hi all!

I am trying to determine what formula to use when calculating Harvey Balls using two different column details. Harvey Balls are in the "Corrective Action" column.

What I want to do:

Harvey Ball to be Full when all children rows are checked in "done" column AND there is text in the "Response" column

Harvey ball to be 3/4 full when text is in the "Response" column and "Done" is not checked

Harvey Ball to be 1/2 full when one "Done" is checked and "Response" has text

Harvey Ball to be empty when nothing is checked.

Thoughts if this is possible? I've taken some tries at this, but I keep getting error messages. Screenshot below!

Paul Newcome

Ok. Give something like this a try...

=IF(COUNTIFS(CHILDREN(Done@row), @cell = 1, CHILDREN(Response@row), @cell <> "") = COUNTIFS(CHILDREN(Done@row), OR(@cell = "", @cell <> "")), "Full", IF(COUNTIFS(CHILDREN(Done@row), @cell = 1, CHILDREN(Response@row), @cell <> "")>= 1, "Half", "Empty"))

If they are all checked and all have responses = "Full"

If at least one is checked and has a response = "Half"

All else = "Empty"

Paul Newcome

A couple of questions...

1) The 1/2 full: Is that when the Done and Response are filled in on the parent row, or if at least one child row has that?

2) 3/4 full: Would that override the above?

3) Exactly which row(s) are you planning on putting this formula in?

Sam Clapp

1. The latter. If one is checked w/ response text to make it 1/2 full.
2. I wouldn't mind nixing this 3/4 full logic as it may override (it's not super important to have logic for all HArvey Ball options
3. I'm planning on putting this in only the parent rows (there are 14 parent rows)

Paul Newcome

Ok. So far we have...

Harvey Ball to be Full when all children rows are checked in "done" column AND there is text in the "Response" column in all child rows

Harvey Ball to be 1/2 full when one "Done" is checked and "Response" has text on at least one child row.

Harvey Ball to be empty when nothing is checked in the child rows.

Additional variables:

What if all children are checked but none have a response?

What if all have a response but none are checked? --> This one would currently be set to "Empty" since nothing is checked.

Clarification on location of formula: In your screenshot, you have two layers of parent rows. Would it be in the topmost parent (black highlight) or the immediate parent ("test text" in Primary Column)?

Sam Clapp

it would be placed in the immediate parents, one without color!

If nothing is in the text and the boxes are checked, empty would be fine, as it would alert something needs to be added!

Thanks for your help :)

Paul Newcome

Ok. Give something like this a try...

=IF(COUNTIFS(CHILDREN(Done@row), @cell = 1, CHILDREN(Response@row), @cell <> "") = COUNTIFS(CHILDREN(Done@row), OR(@cell = "", @cell <> "")), "Full", IF(COUNTIFS(CHILDREN(Done@row), @cell = 1, CHILDREN(Response@row), @cell <> "")>= 1, "Half", "Empty"))

If they are all checked and all have responses = "Full"

If at least one is checked and has a response = "Half"

All else = "Empty"

Sam Clapp

That worked! Whoop! Thanks a ton!!

Paul Newcome

Happy to help. 👍️