Harvey Balls Using Formulas & Multiple Columns
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!
Best Answer

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

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?

 The latter. If one is checked w/ response text to make it 1/2 full.
 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
 I'm planning on putting this in only the parent rows (there are 14 parent rows)

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

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

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"

That worked! Whoop! Thanks a ton!!

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!