Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Multiple Check box columns with Harvey ball formula in symbol column.
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!
Comments
-
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.
-
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"))))
