IF formula correction please
Hi There,
这对我来说是一个星期一的早晨,一个明显的缺乏of coffee means that the above formula is likely an absolute shemozzle. Essentially I am trying to return an argument that says If the Priority rating shows this (eg H5) then return this number (eg 15) in the helper column.
This formula (once correct!) then also need to be entered into a helper column for "Size of Audience & Impact" which also has these values, so I can then use both helper columns for a scatter graph - to create a matrix of sort for priority vs impact.
Saying all of this hurts my head already, please help :)
Thanks
Elise
Best Answers
-
Kelly Moore ✭✭✭✭✭✭
Hey
The Left is what grabs the letter to evaluate. An Unparseable is typically a missing parenthesis, bracket or comma. My formula was working so let me double check what I copied it into the post. This works below
=IF(LEFT([Priority Rating]@row, 1) = "H", VALUE(SUBSTITUTE([Priority Rating]@row, LEFT([Priority Rating]@row, 1), "")) + 10, IF(LEFT([Priority Rating]@row, 1) = "M", VALUE(SUBSTITUTE([Priority Rating]@row, LEFT([Priority Rating]@row, 1), "")) + 5, IF(LEFT([Priority Rating]@row, 1) = "L", VALUE(SUBSTITUTE([Priority Rating]@row, LEFT([Priority Rating]@row, 1), "")))))
-
Kelly Moore ✭✭✭✭✭✭
If your numbers in the Priority rating are always single digits (I wasn't sure they would be so the original formula accounts for that) then you can use this
=IF(LEFT([Priority Rating]@row, 1) = "H", VALUE(RIGHT([Priority Rating]@row, 1)) + 10, IF(LEFT([Priority Rating]@row, 1) = "M", VALUE(RIGHT([Priority Rating]@row, 1)) + 5, IF(LEFT([Priority Rating]@row, 1) = "L", VALUE(RIGHT([Priority Rating]@row, 1)))))
Answers
-
Kelly Moore ✭✭✭✭✭✭
Hey@EliseB
但如果你的嵌套方法是一个有效的方法r it becomes difficult to maintain if your values change or additional responses are added. I tried to provide an approach that was a bit more dynamic. There are other ways this could be done.
This approach checks first to see what the first character is, then adds the appropriate amount depending on that c character. The SUBSTITUTE function produces a text string and the VALUE function converts the text string back into an actual number.
=IF(LEFT([Priority Rating]@row, 1) = "H", VALUE(SUBSTITUTE([Priority Rating]@row, LEFT[Priority Rating]@row, 1), "")) + 10, IF(LEFT([Priority Rating]@row, 1) = "M", VALUE(SUBSTITUTE([Priority Rating]@row, LEFT([Priority Rating]@row, 1), "")) + 5, IF(LEFT([Priority Rating]@row, 1) = "L", VALUE(SUBSTITUTE([Priority Rating]@row, LEFT([Priority Rating]@row, 1), "")))))
Will this work for you?
Kelly
-
EliseB ✭Hi Kelly,
Thank you but no unfortunately that doesn't work for me, it comes up as #Unparseable, I assume that is because the values are H5, H4, H3 etc rather than H alone? I don't understand the need for the LEFT when referencing the Priority rating column either?
I should add that there will be no additional options being entered however there may be an instance where a high priority item moves from say H5 to H4 which is the reason I wanted to write a formula rather than just enter the corresponding numbers below in the fields
-
Kelly Moore ✭✭✭✭✭✭
Hey
The Left is what grabs the letter to evaluate. An Unparseable is typically a missing parenthesis, bracket or comma. My formula was working so let me double check what I copied it into the post. This works below
=IF(LEFT([Priority Rating]@row, 1) = "H", VALUE(SUBSTITUTE([Priority Rating]@row, LEFT([Priority Rating]@row, 1), "")) + 10, IF(LEFT([Priority Rating]@row, 1) = "M", VALUE(SUBSTITUTE([Priority Rating]@row, LEFT([Priority Rating]@row, 1), "")) + 5, IF(LEFT([Priority Rating]@row, 1) = "L", VALUE(SUBSTITUTE([Priority Rating]@row, LEFT([Priority Rating]@row, 1), "")))))
-
Kelly Moore ✭✭✭✭✭✭
If your numbers in the Priority rating are always single digits (I wasn't sure they would be so the original formula accounts for that) then you can use this
=IF(LEFT([Priority Rating]@row, 1) = "H", VALUE(RIGHT([Priority Rating]@row, 1)) + 10, IF(LEFT([Priority Rating]@row, 1) = "M", VALUE(RIGHT([Priority Rating]@row, 1)) + 5, IF(LEFT([Priority Rating]@row, 1) = "L", VALUE(RIGHT([Priority Rating]@row, 1)))))
-
EliseB ✭
Thank you so much Kelly, I had no idea about the left condition, that's a game changer! I tried both the middle and the bottom option and they both work perfectly! Thank you that has saved me hours of trouble and anxiety.
Really appreciate it,
Elise
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/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":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-26T01:04:51+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-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"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-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+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-08-26T17:06:33+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-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
