Enter number in cell based on which checkbox is checked

Options

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

  • Ric T
    Ric T ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @llambert39581,

    Try this formula:

    =IF(AND([Credit 1 Complete]@row = 1, [Credit 2 Complete]@row = 1, [Credit 3 Complete]@row = 1), "6%", IF(AND([Credit 1 Complete]@row = 1, [Credit 2 Complete]@row = 1), "4%", IF([Credit 1 Complete]@row = 1, "2%", "")))

    If a mistake is made like if credit complete 3 complete is ticked, and credit 2 and 1 is not ticked, the value will be blank. You can use conditional formatting to highlight that cell red (or your color of choice) to visualize when that that cell is blank. Let me know if you have any more q's!

    Cheers,

    Ric

Answers

  • Ric T
    Ric T ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @llambert39581,

    Try this formula:

    =IF(AND([Credit 1 Complete]@row = 1, [Credit 2 Complete]@row = 1, [Credit 3 Complete]@row = 1), "6%", IF(AND([Credit 1 Complete]@row = 1, [Credit 2 Complete]@row = 1), "4%", IF([Credit 1 Complete]@row = 1, "2%", "")))

    If a mistake is made like if credit complete 3 complete is ticked, and credit 2 and 1 is not ticked, the value will be blank. You can use conditional formatting to highlight that cell red (or your color of choice) to visualize when that that cell is blank. Let me know if you have any more q's!

    Cheers,

    Ric

  • llambert39581
    Options

    Thank you! This is what I needed!

  • Ric T
    Ric T ✭✭✭✭✭✭
    Options

    Happy to assist!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!