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

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

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

Thank you! This is what I needed!

Happy to assist!
Help Article Resources
Categories
Check out the Formula Handbook template!