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

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

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
Categories
Check out the Formula Handbook template!