What's wrong with this formula?
It returns only Complete or Not Started.
=IF([% Complete]@row = "1", "Complete", IF([% Complete]@row = "75", "End in Sight", IF([% Complete]@row = "50", "Work in Progress", IF([% Complete]@row = "25", "Just Started", "Not Started"))))
Best Answers
-
Ken Hankoff ✭✭✭✭
Thanks, Paul. Close, but I'm still missing something.
What I'm trying to do is build a formula with logic that evaluates and returns as follows, and I think I've perhaps got things in the wrong order:
If % Complete is 0 display "Not Started"
If % Complete is greater than zero and up to 25% display "Just Started"
If % Complete is greater than 25% and less than 75% display "Work in Progress"
If % Complete is greater than 75% and less than 100% display "End in Sight"
If % Complete is 100% display Complete
Again - I think I'm close with the formula I built and your AND suggestion, but still not working.
Thoughts?
Here it is again: =IF([% Complete]@row = "1", "Complete", IF(AND([% Complete]@row < "1", [% Complete]@row > ".75"), "End in Sight", IF(AND([% Complete]@row < ".75", [% Complete]@row > ".25"), "Work in Process", IF(AND([% Complete]@row < ".25", [% Complete]@row > ".0"), "Just Started", "Not Started"))))
-
Paul Newcome ✭✭✭✭✭✭Ok. So let's start off with some "Nested IF Rules"...
Nested IF statements work from left to right and stop on the first true value. So if it makes it to the 3rd IF, then by default the first 2 must be false. That means we don't have to specify that in the 3rd since it is already assumed.
Nested IF statements can frequently be written very similar to how you would read exactly what your criteria is in that last post.
If % Complete is 0 display "Not Started"
If % Complete is greater than zero and up to 25% display "Just Started"
If % Complete is greater than 25% and less than 75% display "Work in Progress"
If % Complete is greater than 75% and less than 100% display "End in Sight"
If % Complete is 100% display Complete
Since we can say that previous arguments are assumed to be false just by the formula "skipping over them", we can rewrite your criteria like so...
If % Complete is 0 display "Not Started"
If % Complete is less than 25% display "Just Started"
If % Complete is less than 75% display "Work in Progress"
If % Complete is less than 100% display "End in Sight"
If % Complete is 100% display "Complete"
Now we can read down the list and it will read exactly like a nested IF.
=IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row <= .25, "Just Started", IF([% Complete]@row <= .75, "Work In Progress", IF([% Complete]@row < 1, "End In Sight", IF([% Complete]@row = 1, "Complete")))))
Answers
-
Andrée Starå ✭✭✭✭✭✭Smartsheet looks at the numbers in a column formatted for percentage as values between 0 and 1.
You'd need to use decimal values instead for it to work.
25% = 0,25 (0.25)
50% = 0,5 (0.5)
100% = 1
Depending on your country/region, you'll need to exchange the comma to a period.
Did that work?
I hope that helps!
Be safe and have a fantastic day!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Ken Hankoff ✭✭✭✭
Hi Andree,
工作(后我做你适合你的改变d, and figured out that .050 wouldn't work :-).
I've also got a bigger challenge for you. I'm wondering if there is a way to create a formula that takes this a step further, so that it works on the Parent rows in the hierarchy.
In other words, when the Parent row in the % Complete column automatically (because dependencies are enabled) calculates a value (e.g. 28%), can rules for the value in the Status column (where this formula sits) be made to return a similar result? For example, anything greater than 0% and less than 25.01% would be "Just Started". Anything between 25.01% and 50.01% would be "Work in Progress", etc.
Can it be done?
-
Ken Hankoff ✭✭✭✭
In the event you think I'm just mooching off of your brilliance (Andree), I did want you to know I am attempting to solve this one on my own.
Although it is not (yet) functioning as expected, I do feel I am getting close with this formula:
=IF([% Complete]@row = "1", "Complete", IF(OR([% Complete]@row < "1", [% Complete]@row > ".75"), "End in Sight", IF(OR([% Complete]@row < ".75", [% Complete]@row > ".25"), "Work in Process", IF(OR([% Complete]@row < ".25", [% Complete]@row > ".0"), "Just Started", "Not Started"))))
-
Paul Newcome ✭✭✭✭✭✭
Try replacing your OR functions with AND functions.
-
Ken Hankoff ✭✭✭✭
Thanks, Paul. Close, but I'm still missing something.
What I'm trying to do is build a formula with logic that evaluates and returns as follows, and I think I've perhaps got things in the wrong order:
If % Complete is 0 display "Not Started"
If % Complete is greater than zero and up to 25% display "Just Started"
If % Complete is greater than 25% and less than 75% display "Work in Progress"
If % Complete is greater than 75% and less than 100% display "End in Sight"
If % Complete is 100% display Complete
Again - I think I'm close with the formula I built and your AND suggestion, but still not working.
Thoughts?
Here it is again: =IF([% Complete]@row = "1", "Complete", IF(AND([% Complete]@row < "1", [% Complete]@row > ".75"), "End in Sight", IF(AND([% Complete]@row < ".75", [% Complete]@row > ".25"), "Work in Process", IF(AND([% Complete]@row < ".25", [% Complete]@row > ".0"), "Just Started", "Not Started"))))
-
Paul Newcome ✭✭✭✭✭✭
Ok. So let's start off with some "Nested IF Rules"...
Nested IF statements work from left to right and stop on the first true value. So if it makes it to the 3rd IF, then by default the first 2 must be false. That means we don't have to specify that in the 3rd since it is already assumed.
Nested IF statements can frequently be written very similar to how you would read exactly what your criteria is in that last post.
If % Complete is 0 display "Not Started"
If % Complete is greater than zero and up to 25% display "Just Started"
If % Complete is greater than 25% and less than 75% display "Work in Progress"
If % Complete is greater than 75% and less than 100% display "End in Sight"
If % Complete is 100% display Complete
Since we can say that previous arguments are assumed to be false just by the formula "skipping over them", we can rewrite your criteria like so...
If % Complete is 0 display "Not Started"
If % Complete is less than 25% display "Just Started"
If % Complete is less than 75% display "Work in Progress"
If % Complete is less than 100% display "End in Sight"
If % Complete is 100% display "Complete"
Now we can read down the list and it will read exactly like a nested IF.
=IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row <= .25, "Just Started", IF([% Complete]@row <= .75, "Work In Progress", IF([% Complete]@row < 1, "End In Sight", IF([% Complete]@row = 1, "Complete")))))
-
Ken Hankoff ✭✭✭✭
That's great, Paul. Your suggested formula adjustment worked.
I was wondering whether my use of OR and AND was somehow overkill. Clearly you've shown that it was. I need to work on my understanding of the flow of the logic, as this should not be so difficult (for me).
Thank you!!
-
Andrée Starå ✭✭✭✭✭✭
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Paul Newcome ✭✭✭✭✭✭
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":"
