Enter number in cell based on which checkbox is checked

I'm new at this so patience is appreciated.

I want to fill a cell with 2%, 4% or 6% based on one, two or three checkboxes. If checkbox Credit 1 Complete is checked, Credit Obtained = 2%; Credit 2 complete, Credit Obtained =4%; and Credit 3 complete checked, credit obtained =6%. Credit Obtained cannot be 6% without the other two boxes being checked. Any suggestions on how to write a formula for this?

Thank you!

Best Answer

  • JamesB
    JamesB ✭✭✭✭✭✭
    Answer ✓

    @llambert39581

    I believe based on your request the following formula should work for you.

    =SUM(IF([Credit 1 Complete]@row = 1, 0.02, 0) + IF([Credit 2 Complete]@row = 1, 0.02, 0) + IF([Credit 3 Complete]@row = 1, 0.02, 0))

    To break this down. The SUM formula will add up the 3 nested IF statements based on whether the box for that column in the row is checked.

    When determining the status of a checkbox in a formula, it is 1 or 0 that is looking for. So in each if statement We check the status of the checkbox for the credit checkboxes individually and assign a decimal value of .02 if the statement is true and a 0 if it is false. This formula will yield a 0% for all unchecked, a 2% for any 1 of the 3 checked, a 4% for any 2 of the 3 checked and a 6% if all three are checked.

    Hope this gives you what you need.

Answers

  • JamesB
    JamesB ✭✭✭✭✭✭
    Answer ✓

    @llambert39581

    I believe based on your request the following formula should work for you.

    =SUM(IF([Credit 1 Complete]@row = 1, 0.02, 0) + IF([Credit 2 Complete]@row = 1, 0.02, 0) + IF([Credit 3 Complete]@row = 1, 0.02, 0))

    To break this down. The SUM formula will add up the 3 nested IF statements based on whether the box for that column in the row is checked.

    When determining the status of a checkbox in a formula, it is 1 or 0 that is looking for. So in each if statement We check the status of the checkbox for the credit checkboxes individually and assign a decimal value of .02 if the statement is true and a 0 if it is false. This formula will yield a 0% for all unchecked, a 2% for any 1 of the 3 checked, a 4% for any 2 of the 3 checked and a 6% if all three are checked.

    Hope this gives you what you need.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!