# Harvey Balls Using Formulas & Multiple Columns

Options
✭✭✭

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!

Tags:

• ✭✭✭✭✭✭
Options

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"

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭
Options
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)

• ✭✭✭✭✭✭
Options

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.

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

• ✭✭✭
Options

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!

• ✭✭✭✭✭✭
Options

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"

• ✭✭✭
Options

That worked! Whoop! Thanks a ton!!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!