Need help with creating aging buckets.
Hello, I have the following formula in excel but it did not transfer to Smartsheet. Is there a way to get this formula over?
=LOOKUP(F2,{-100,7,14,21,30,45,90,180},{"0-7","7-14","14-21","21-30",">30",">45",">90",">180"})
I am entering this formula in the column "Equipment Date Range" and validating the data on column "Days Onsite", for the example below all those days on site should return ">90" but currently getting an error. Any help would be appreciated.
Best Answer
-
Debbie Sawyer ✭✭✭✭✭✭This sort of works for me:
=IF(AND([Days Onsite]@row >= 0, [Days Onsite]@row <= 7), "0-7", IF(AND([Days Onsite]@row >= 8, [Days Onsite]@row <= 14), "
8-14", IF(AND([Days Onsite]@row >= 15, [Days Onsite]@row <= 21), "15-21", IF(AND([Days Onsite]@row >= 22, [Days Onsite]@row <= 30), "22-30", IF(AND([Days Onsite]@row >= 31, [Days Onsite]@row <= 44), ">30", IF(AND([Days Onsite]@row >= 45, [Days Onsite]@row <= 89), ">45", IF(AND([Days Onsite]@row >= 90, [Days Onsite]@row <= 179), ">90", ">180")))))))I have tweaked the numbers slightly as if something is 7 it is in the 0-7 not the 7-14 and if something is 14 then it is in the 8-14 not the 14-21 etc...
Can be tweaked back :)
Good luck
(you can hide the Days Onsite column...)
Answers
-
Debbie Sawyer ✭✭✭✭✭✭
嗨虹膜
Are you able to explain the logic that you are trying to achieve? Should the Equipment Date Range be returning a value of 0-7 or 7-14 etc based on the number of days between Delivery Date and Pick Up Date?
Is that what you require?
Kind regards
Debbie
(PS - without seeing the Excel sheet the formula that hasn't worked is tricky to unpick - it would be easier to find out what you need the Equipment Date Range to be returning and we can help from there)
-
Debbie Sawyer ✭✭✭✭✭✭
This sort of works for me:
=IF(AND([Days Onsite]@row >= 0, [Days Onsite]@row <= 7), "0-7", IF(AND([Days Onsite]@row >= 8, [Days Onsite]@row <= 14), "
8-14", IF(AND([Days Onsite]@row >= 15, [Days Onsite]@row <= 21), "15-21", IF(AND([Days Onsite]@row >= 22, [Days Onsite]@row <= 30), "22-30", IF(AND([Days Onsite]@row >= 31, [Days Onsite]@row <= 44), ">30", IF(AND([Days Onsite]@row >= 45, [Days Onsite]@row <= 89), ">45", IF(AND([Days Onsite]@row >= 90, [Days Onsite]@row <= 179), ">90", ">180")))))))I have tweaked the numbers slightly as if something is 7 it is in the 0-7 not the 7-14 and if something is 14 then it is in the 8-14 not the 14-21 etc...
Can be tweaked back :)
Good luck
(you can hide the Days Onsite column...)
Help Article Resources
Categories
=DATEONLY(Created@row) + IF(AND(FIND(\"P\", Created@row)> 0, VALUE(MID(Created@row, FIND(\" \", Created@row) + 1, FIND(\":\", Created@row) - (FIND(\" \", Created@row) + 1))) >= 4, VALUE(MID(Created@row, FIND(\" \", Created@row) + 1, FIND(\":\", Created@row) - (FIND(\" \", Created@row) + 1))) < 12), 1, 0)<\/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":227,"urlcode":"api-and-developers","name":"API and Developers"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":277,"urlcode":"Integrations","name":"Integrations"},{"tagID":334,"urlcode":"automations","name":"Automations"},{"tagID":369,"urlcode":"bridge-by-smartsheet","name":"Bridge"}]},{"discussionID":108222,"type":"question","name":"Parent Status based on Children Statuses - Need help refining this formula","excerpt":"Hey Smartsheet Community! I have a beast of a formula that has probably gotten a bit away from me, but I need help refining this so it meets certain criteria per our stakeholders vision. Here's the formula: =IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), \"Complete\"), \"Complete\", IF(COUNT(CHILDREN()) = AND(COUNTIF(CHILDREN(),…","snippet":"Hey Smartsheet Community! I have a beast of a formula that has probably gotten a bit away from me, but I need help refining this so it meets certain criteria per our stakeholders…","categoryID":322,"dateInserted":"2023-07-27T20:58:59+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T15:08:32+00:00","insertUserID":146258,"insertUser":{"userID":146258,"name":"dhall","url":"https:\/\/community.smartsheet.com\/profile\/dhall","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T15:09:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-28T15:08:41+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":33,"score":null,"hot":3381050851,"url":"https:\/\/community.smartsheet.com\/discussion\/108222\/parent-status-based-on-children-statuses-need-help-refining-this-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108222\/parent-status-based-on-children-statuses-need-help-refining-this-formula","format":"Rich","tagIDs":[219,254],"lastPost":{"discussionID":108222,"commentID":387807,"name":"Re: Parent Status based on Children Statuses - Need help refining this formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387807#Comment_387807","dateInserted":"2023-07-28T15:08:32+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-28T15:08:41+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/P3FFJGV2II8T\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T12:21:14+00:00","dateAnswered":"2023-07-28T00:26:52+00:00","acceptedAnswers":[{"commentID":387717,"body":"
Give this a try:<\/p>
=IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), @cell = \"Canceled\"), \"Canceled\", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), OR(@cell = \"Complete\", @cell = \"Canceled\", @cell = \"On Hold\")), \"Complete\", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), @cell = \"Not Started\"), \"Not Started\", IF(COUNTIFS(CHLDREN(), @cell = \"Upcoming\")> 0, \"Upcoming\", \"In Progress\"))))<\/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":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108217,"type":"question","name":"\"Review status\" based on multiple checkboxes","excerpt":"Hello! I need help trying to figure out formulas that triggers the review status to green or red based on how many checkboxes that represent each month of the year are checked off. The boxes that are checked off would be based off on the frequency column which is semi-annually, annually, bi-weekly, daily, quarterly,…","snippet":"Hello! I need help trying to figure out formulas that triggers the review status to green or red based on how many checkboxes that represent each month of the year are checked…","categoryID":322,"dateInserted":"2023-07-27T20:20:30+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T15:16:42+00:00","insertUserID":164178,"insertUser":{"userID":164178,"name":"cware10","url":"https:\/\/community.smartsheet.com\/profile\/cware10","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!AmDrR3_JMno!ejC0B2rXGOU!0QaX9eQMbnZ","dateLastActive":"2023-07-28T15:16:02+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164178,"lastUser":{"userID":164178,"name":"cware10","url":"https:\/\/community.smartsheet.com\/profile\/cware10","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!AmDrR3_JMno!ejC0B2rXGOU!0QaX9eQMbnZ","dateLastActive":"2023-07-28T15:16:02+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":32,"score":null,"hot":3381049032,"url":"https:\/\/community.smartsheet.com\/discussion\/108217\/review-status-based-on-multiple-checkboxes","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108217\/review-status-based-on-multiple-checkboxes","format":"Rich","tagIDs":[219,238,254,440],"lastPost":{"discussionID":108217,"commentID":387813,"name":"Re: \"Review status\" based on multiple checkboxes","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387813#Comment_387813","dateInserted":"2023-07-28T15:16:42+00:00","insertUserID":164178,"insertUser":{"userID":164178,"name":"cware10","url":"https:\/\/community.smartsheet.com\/profile\/cware10","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!AmDrR3_JMno!ejC0B2rXGOU!0QaX9eQMbnZ","dateLastActive":"2023-07-28T15:16:02+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/68S9XTNSOICR\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T15:15:59+00:00","dateAnswered":"2023-07-28T15:06:10+00:00","acceptedAnswers":[{"commentID":387806,"body":"
My suggestion would be to use an IF\/AND\/COUNTIFS combo for all of them then.<\/p>
COUNTIFS(Jan@row:Dec@row, @cell = 1)<\/p>
<\/p>
The above will tell you how many boxes are checked. You can combine this in an IF statement with an AND function to say that IF the Frequency@row = \"x\" AND the COUNTIFS() = #, \"Green\".<\/p>
<\/p>
=IF(AND(Frequency@row = \"Monthly\", COUNTIFS(Jan@row:Dec@row, @cell = 1) = 12)<\/strong>, \"Green\", \"Red\")<\/p> Then we can write out multiple AND functions using the same logic (frequency and count) and combine them with an OR function.<\/p> =IF(OR(AND(.....), AND(.....), AND(.....)), \"Green\", \"Red\")<\/p> You should be able to use copy\/paste to maintain consistency in your AND functions and just update the actual frequency and required count for each.<\/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":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":238,"urlcode":"Finance","name":"Finance"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}]}],"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":[]}">
<\/p>
<\/p>
