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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 405 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!