Nested IF & Multiple arguments
Hey there!
Currently trying to automate a tracking report that my company uses. I want the "District" column to automate based on the property number entered. However, while there is only 7 districts, the list of properties could easily be in the thousands. We can determine the district of a property by the first two digits, so I started with a LEFT statement, to pull those two digits out. I then tried to create a nested IF statement that looks like this:
=IF([LEFT Function]1 = "01", "4", IF([LEFT Function]1 = "02", "2", IF([LEFT Function]1 = "03", "1", IF([LEFT Function]1 = "04", "5", IF([LEFT Function]1 = "05", "6", IF([LEFT Function]1 = "06", "1", IF([LEFT Function]1 = "07", "7", IF([LEFT Function]1 = "08", "7", IF([LEFT Function]1 = "09", "7", IF([LEFT Function]1 = "10", "4"))))))))))
On this small of a scale, it worked wonderfully. However, there are a total of sixty-two 2-digit codes that need to be in the IF statement for this automation to work across the board. I tried writing out the full 62 IF statements in a long string by copy & pasting the string that worked and just modifying the values, but it came back #UNPARSEABLE.
Is it because i have too many arguments? Has anyone else tried an IF statement on this scale, or know of any other way to work this out?
Comments
-
Mike Wilday ✭✭✭✭✭✭I'm not sure if there are limitations on arguments, but the slightest error can mess it up. I've seen UNPARSEABLE in the following cases:
- when I used a word processor to create the formula. It autocorrected my straight quotes into smart quotes which messes with Smartsheet formulas. Always use a simple text editor.
- When I didn't have the right number of closing parenthesis. There is a little trick I learned from another community member to delete all the closing parenthesis at the end of the formula. Smartsheet will close them all for you. No need to count.
- Also, any misspellings on the column names can throw an unparseable.
If you want to share the formula I can quickly scan it for you for anything I can see.
-
Using nested IF statements does not scale well. Instead you should use a VLOOKUP function to do the decoding. I've attached a screenshot that shows the formula you would need
-
Paul Newcome ✭✭✭✭✭✭I am going to second Andrew's suggestion. The only thing I would add is that you may need to use VALUE(LEFT(..............., 2), ...................................) depending on how your table is built.
thinkspi.com
-
Mike Wilday ✭✭✭✭✭✭
Yes, I agree with them, a vlookup would allow you to keep a list that you could edit and manage outside of each individual formula. Which would be far easier to maintain. You could also do Vlookup with a cross-sheet reference.
-
BreannaS ✭
Thank you! I will follow the VLOOKUP recommendation below, but I appreciate the offer to look it over for me. I will also keep in mind your suggestion to stick to simple text editors in the future so I can hopefully avoid this error.
-
BreannaS ✭
I appreciate it! I wasn't sure how large I could scale an IF statement so this helps me a lot. I will try the VLOOKUP with the above formula - thank you!
-
Paul Newcome ✭✭✭✭✭✭
The only limitations to the scaling would be the 4,000 characters per cell limit. Otherwise you can have as many IF statements strung together as you need. It may not be very user friendly after a certain point, but there isn't a limit as to how many functions you nest together.
thinkspi.com
-
BreannaS ✭
Out of curiosity, I reviewed my nested if statement with 62 arguments - found ONE smart quote. Fixed it and now the whole statement works! awesome
-
Paul Newcome ✭✭✭✭✭✭
62?!? You are a braver soul than I. Hahaha. Once I get past 3 or 4 I move it to a table.
thinkspi.com
-
Mike Wilday ✭✭✭✭✭✭
It's a shame that one small thing like that upsets the whole apple cart. Glad you were able to get working. That is a beast of an if-statement. I've created several like it in my day. Good job and good luck maintaining that beast!
Help Article Resources
Categories
I think this should work:<\/p>
=IF(COUNTIF(Referencia:Referencia, Referencia@row) > 1, 1)<\/p>
Have a great day.<\/p>
Matt<\/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":107890,"type":"question","name":"Need Help with Formula to Roll Up and Average Schedule Health.","excerpt":"Need Help with Formula to Roll Up and Average Schedule Health. I have one that now updates the Schedule Health RYG based on the Start Date\/End Date and Task Status. Next, I am trying to roll up\/average those Individual Task schedule health values to an overall Phase schedule health value and then ultimately to the Overall…","snippet":"Need Help with Formula to Roll Up and Average Schedule Health. I have one that now updates the Schedule Health RYG based on the Start Date\/End Date and Task Status. Next, I am…","categoryID":322,"dateInserted":"2023-07-21T00:56:52+00:00","dateUpdated":null,"dateLastComment":"2023-07-23T11:39:03+00:00","insertUserID":163738,"insertUser":{"userID":163738,"name":"BArmendariz","url":"https:\/\/community.smartsheet.com\/profile\/BArmendariz","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtcJ5xYSxEGHhOSSWfKvut9ttlA26d6GuoryTax_h13HbQ=s96-c","dateLastActive":"2023-07-21T16:10:48+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":163540,"lastUser":{"userID":163540,"name":"stevediaz","title":"mr","url":"https:\/\/community.smartsheet.com\/profile\/stevediaz","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtfxw60LesP__pWW-huKx7XxMBqFzxmYEVfP7e4_MbE-=s96-c","dateLastActive":"2023-07-23T11:39:49+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":49,"score":null,"hot":3380018755,"url":"https:\/\/community.smartsheet.com\/discussion\/107890\/need-help-with-formula-to-roll-up-and-average-schedule-health","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107890\/need-help-with-formula-to-roll-up-and-average-schedule-health","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107890,"commentID":386551,"name":"Re: Need Help with Formula to Roll Up and Average Schedule Health.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386551#Comment_386551","dateInserted":"2023-07-23T11:39:03+00:00","insertUserID":163540,"insertUser":{"userID":163540,"name":"stevediaz","title":"mr","url":"https:\/\/community.smartsheet.com\/profile\/stevediaz","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtfxw60LesP__pWW-huKx7XxMBqFzxmYEVfP7e4_MbE-=s96-c","dateLastActive":"2023-07-23T11:39:49+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-21T16:11:26+00:00","dateAnswered":"2023-07-21T12:51:20+00:00","acceptedAnswers":[{"commentID":386383,"body":"
