Avoiding a massive nested IF formula - checkboxes?
Hi all,
I have 5 check boxes and based on these being ticked or unticked i want to display something in another cell.
It is essentially a decision tree that simply i want to work as follows:
If Group 2 is not ticked (or nothing ticked) = Group 1
If Group 2 is ticked but 3, 4, 5 & Close are not = Group 2
If Groups 2 & 3 are ticked but 4, 5 & Close are not = Group 3
If Groups 2, 3 & 4 are ticked but 5 and close are not = Group 4
If Groups 2, 3, 4 & 5 are ticked but close is not = Group 5
If Group 2, 3, 4, 5 & Close are ticked (all are ticked) = Close
Any ways of doing this simpler than building in every possible scenario via nested IFs? It is a linear structure in that 3 will not be ticked before 2 for example which i know avoids me having to do every combination.
Any help is much appreciated.
Thanks,
Jack
Best Answer
-
Paul Newcome ✭✭✭✭✭✭Work backwards with your IF statements. Nested IFs stop on the first true value, so if it makes it to the next one, then the previous IF(s) MUST be false.
=IF([email protected]= 1, "Close",
Starting with this means that everything that follows automatically assumes that[email protected]is NOT checked.
=IF([email protected]= 1, "Close",IF([Group 5]@row = 1, "Group 5",
Anything coming after this portion automatically assumes that[email protected]and [Group 5]@row are both unchecked. So we continue with this logic that "if it has made it this far then everything before it must be false" and work (almost) the rest of your requirements in using the same logic.
=IF([email protected]= 1, "Close", IF([Group 5]@row = 1, "Group 5",IF([Group 4]@row = 1, "Group 4", IF([Group 3]@row = 1, "Group 3", IF([Group 2]@row = 1, "Group 2",
Now that we only have one option left (because to get this far everything prior must be false which means all of those boxes are unchecked), we can just use the "if false" portion of the final IF statement to say that if everything prior is false, "Group 1". Then we close out all of the IF statements all at once and your formula is complete.
=IF([email protected]= 1, "Close", IF([Group 5]@row = 1, "Group 5", IF([Group 4]@row = 1, "Group 4", IF([Group 3]@row = 1, "Group 3", IF([Group 2]@row = 1, "Group 2","Group 1")))))
thinkspi.com
Answers
-
Paul Newcome ✭✭✭✭✭✭
Work backwards with your IF statements. Nested IFs stop on the first true value, so if it makes it to the next one, then the previous IF(s) MUST be false.
=IF([email protected]= 1, "Close",
Starting with this means that everything that follows automatically assumes that[email protected]is NOT checked.
=IF([email protected]= 1, "Close",IF([Group 5]@row = 1, "Group 5",
Anything coming after this portion automatically assumes that[email protected]and [Group 5]@row are both unchecked. So we continue with this logic that "if it has made it this far then everything before it must be false" and work (almost) the rest of your requirements in using the same logic.
=IF([email protected]= 1, "Close", IF([Group 5]@row = 1, "Group 5",IF([Group 4]@row = 1, "Group 4", IF([Group 3]@row = 1, "Group 3", IF([Group 2]@row = 1, "Group 2",
Now that we only have one option left (because to get this far everything prior must be false which means all of those boxes are unchecked), we can just use the "if false" portion of the final IF statement to say that if everything prior is false, "Group 1". Then we close out all of the IF statements all at once and your formula is complete.
=IF([email protected]= 1, "Close", IF([Group 5]@row = 1, "Group 5", IF([Group 4]@row = 1, "Group 4", IF([Group 3]@row = 1, "Group 3", IF([Group 2]@row = 1, "Group 2","Group 1")))))
thinkspi.com
-
Gunn_Jack ✭✭✭✭
Thank you Paul, that perfect.
Jack
-
Paul Newcome ✭✭✭✭✭✭
Help Article Resources
Categories
@SarahI<\/a> Yes you can do that just use CHILDREN([COLUMN NAME])<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":106901,"type":"question","name":"Can you use a COUNTIF formula to county symbols?","excerpt":"I have a list of goals for the year and we are using harvey ball symbols to indicate the percent complete an the red, yellow, green, gray balls to indicate the health\/status of the project. I would like to display a report on my dashboard that shows the number of projects we have in each harvey ball stage and also in each…","categoryID":322,"dateInserted":"2023-06-26T15:59:48+00:00","dateUpdated":"2023-06-26T16:00:12+00:00","dateLastComment":"2023-06-26T16:29:45+00:00","insertUserID":162764,"insertUser":{"userID":162764,"name":"Stephanie D","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Stephanie%20D","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mctWfgWrL7o!n-DX3ymVfmQ!msa32mT4_k9","dateLastActive":"2023-06-26T16:52:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":162764,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-27T06:21:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3375593973,"url":"https:\/\/community.smartsheet.com\/discussion\/106901\/can-you-use-a-countif-formula-to-county-symbols","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106901\/can-you-use-a-countif-formula-to-county-symbols","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106901,"commentID":382346,"name":"Re: Can you use a COUNTIF formula to county symbols?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382346#Comment_382346","dateInserted":"2023-06-26T16:29:45+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-27T06:21:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-26T16:24:48+00:00","dateAnswered":"2023-06-26T16:08:42+00:00","acceptedAnswers":[{"commentID":382328,"body":" Hi @Stephanie D<\/a> <\/p> I hope you're well and safe!<\/p> Try something like this.<\/p> =COUNTIF(Status:Status, \"Green\")<\/p> =COUNTIF([% Complete]:[% Complete], \"Half\")<\/p> Did that work\/help? <\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
