Multiple Check box columns with Harvey ball formula in symbol column.

NFujihira

I have four columns set to check boxes. How do I set the formula to show the empty Harvey ball if none of the check boxes are checked, a quarter Harvey ball with one check box checked, a half Harvey box with two check boxes checked, a three quarter Harvey ball with three check boxes checked and a full Harvey box when all of the check boxes are checked? 

Thank you for your help!

KrisWalsh

Temporarily name your checkbox columns to A, B, C & D.

=IF($D1 = 1, "Full", IF($C1 = 1, "Three Quarter", IF($B1 = 1, "Half", IF($A1 = 1, "Quarter", "Empty"))))

Once the formula is in place & working, change the column names back to whatever they were before & the formula will update accordingly.

MarkLindsey

If you don't care which check box is checked, you can create a hidden column to calculate how many checkboxes are checked.  Then use that to drive the Harvey ball.

I've named each checkbox column Chk1, Chk2, Chk3, Chk4.  I've named the hidden column ChkCount.

For the hidden column, use this formula:

=COUNTIF([Chk1]1:[Chk4]1, =1)

 

For the Harvey ball column, use this formula:

=IF(ChkCount1 = 0, "Empty", IF(ChkCount1 = 1, "Quarter", IF(ChkCount1 = 2, "Half", IF(ChkCount1 = 3, "Three Quarter", "Full"))))