Seeking correct formula for "If greater than, but less than" statement.
Ifcell Ais> 30, but< 61, thencell Bis "31 - 60"
Best Answer
-
I finally figured it out: =IF(AND([Days Open]4 > 60, [Days Open]4 < 91), "61-90")
Thank you for your time!
Answers
-
Mike Wilday ✭✭✭✭✭✭Try this... You only need brackets around the column names if they have spaces. the 1 after the column name = the row you are are specifying.
=IF(And([Column A Name]1=>30, [Column B Name]1=<61), "31-60", "")
If the item doesn't meet your criteria then the result will be blank.
-
Mike, Thanks for the reply. I explained problem incorrectly.
cellA15has the number31in it.
I need cell B15 to determine if cell A15 has a number in it that isgreater than 30, butless than 61.
If it does, then I wantB15to say "31-60"
Thank you for your assistance!
-
Mike Wilday ✭✭✭✭✭✭
While Excell and Google Sheets reference columns with Letters, formulas in Smartsheet reference the column names. Hence A15 would be [Column A Name]15 if there are spaces in the column name or ColumnName15 if there are no spaces.
See the following examples and replace Column A name is the name of Column A.
=IF(And([Column A Name]15=>30, [Column B Name]15=<61), "31-60", "")
=IF(And(ColumnAName15=>30, ColumnBName15=<61), "31-60", "")
-
I understand about the columns... it was a poor example to use.
But the formula isn't working...and I'm unclear why you placedColumn B Namewhere you did?In my mind, that should also beColumn A Name.Column B Nameis where I want the text to go based on Column A....but that didn't work either. Both produced error: #INVALID OPERATION
IF(And([Column A Name]15=>30, [ColumnBName]15=<61), "31-60", "")
Maybe the attached screenshot will help? If not, I wont bug you anymore
-
I finally figured it out: =IF(AND([Days Open]4 > 60, [Days Open]4 < 91), "61-90")
Thank you for your time!
-
Mike Wilday ✭✭✭✭✭✭
Yep - sorry I got confused by the Column A and Column B thing.
Good job. Glad I helped get you onto the right track!If you want the formula to give you the run down for each of them, nest the formulas...
=IF([Days Open]4>30,"Less than 30", IF(AND([Days Open]4 > 30, [Days Open]4 < 61), "30-60", IF(AND([Days Open]4 > 60, [Days Open]4 < 91), "61-90", IF(AND([Days Open]4 > 90, [Days Open]4 < 120), "91-120", IF([Days Open]4 > 120, "Greater than 120")))))
-
J. Craig Williams ✭✭✭✭✭✭This last formula has at least two errors in it
=IF([Days Open]4 > 30,"Less than 30", IF(AND([Days Open]4 > 30, [Days Open]4 < 61), "30-60", IF(AND([Days Open]4 > 60, [Days Open]4 < 91), "61-90", IF(AND([Days Open]4 > 90, [Days Open]4 < 120), "91-120", IF([Days Open]4 > 120, "Greater than 120")))))The first check for for greater than 30, but should have been less than.
The second check was for greater than 30, but should have been greater than or equal to
Further, here is a shorter version:
=IF([Days Open]11 > 120, "Greater than 120", IF([Days Open]11 > 90, "91-120", IF([Days Open]11 > 60, "61-90", IF([Days Open]11 > 30, "31-60", "30 or Less"))))I dispense with the AND() because I let order of the function dictate where the value lies. If it is 90, I know already that it is not greater than 120 and is not greater than 90.
嵌套是很重要的,不过不要让她的老公知道the way we describe the problem, but think about the way the computer will understand it.
Craig
-
Mike Wilday ✭✭✭✭✭✭
I need to slow down. Sheesh. Thanks for catching those errors, Craig. Good thinking.
-
J. Craig Williams ✭✭✭✭✭✭
A little testing goes a long way, Mike. I'm guilty of sacrificing quality for speed as well, but have been burned so often in my previous line of work, I get a physical pain when I know I am doing it.
A wrong answer is worse than no answer at all.
Craig
-
Trying to have a column post a statement based on another columns %
<1% Not Started
100% Complete
>1% thru <100% In Progress
I can get the not started and Complete to work but I get an error for the in-between
Here is the formula I have used
=IF([% Complete]90 =0, "Not Started", IF([% Complete]90 = 1, "Complete", IF(AND([% Complete]90, >.01, [% Complete]90, <.99, "In Progress"))))
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-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"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-27T02:16:35+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":25,"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":"
