Formula to check a box if a condition is met, if not it must stay unchecked

Hi all,

I have 3 columns, and I need columns "Bonus Y" and "Bonus N" to check if "Bonus Eligible" says "Yes" or "No" and if it does not have any data, then the check boxes must remain unchecked.

I have the following formulas in the columns:

Bonus Y: =IF([Bonus Eligible]@row = "yes"; 1; 0)

Bonus N: =IF([Bonus Eligible]@row = "yes"; 0; 1)

So my formulas work when there is data in column "Bonus Eligible", but when it's empty then "Bonus N"'s check box is selected. I only need the boxes checked according to the Yes and No condition. Please can you help?

Thanks

Best Answer

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Answer ✓

    @Desire

    You have your Bonus N formula's 0 and 1 backwards.

    =IF([Bonus Eligible]@row ="no";1,0)

    The way that you have it written in your example is to check if the IF is NOT true. If we want it to be check if it IS true, the 1 needs to come first.

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

Answers

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭

    @Desire

    Would it be viable for in your Bonus N to just change the parameter to check for no?

    =IF([Bonus Eligible]@row = "no"; 1; 0)

    Based on that, it would leave all that do not apply unchecked, the same way your yes formula does.

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

  • Desire
    Desire ✭✭

    @Colleen Patterson

    Thank you for your assistance.

    Unfortunately it's not helping, it is now checking both columns (Bonus Y and Bonus N) if there is Yes in, and when there is No, then it removes the check, and when there is nothing then Bonus N is still checked.

    Bonus Y: =IF([Bonus Eligible]@row = "yes"; 1; 0)

    Bonus N: =IF([Bonus Eligible]@row = "no"; 0; 1)

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Answer ✓

    @Desire

    You have your Bonus N formula's 0 and 1 backwards.

    =IF([Bonus Eligible]@row ="no";1,0)

    The way that you have it written in your example is to check if the IF is NOT true. If we want it to be check if it IS true, the 1 needs to come first.

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

  • Desire
    Desire ✭✭

    Oh my word, it is working......thank you, you are amazing!!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!