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},{"07","714","1421","2130",">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

This sort of works for me:
=IF(AND([Days Onsite]@row >= 0, [Days Onsite]@row <= 7), "07", IF(AND([Days Onsite]@row >= 8, [Days Onsite]@row <= 14), "
814", IF(AND([Days Onsite]@row >= 15, [Days Onsite]@row <= 21), "1521", IF(AND([Days Onsite]@row >= 22, [Days Onsite]@row <= 30), "2230", 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 07 not the 714 and if something is 14 then it is in the 814 not the 1421 etc...
Can be tweaked back :)
Good luck
(you can hide the Days Onsite column...)
Answers

Hi Iris
Are you able to explain the logic that you are trying to achieve? Should the Equipment Date Range be returning a value of 07 or 714 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)

This sort of works for me:
=IF(AND([Days Onsite]@row >= 0, [Days Onsite]@row <= 7), "07", IF(AND([Days Onsite]@row >= 8, [Days Onsite]@row <= 14), "
814", IF(AND([Days Onsite]@row >= 15, [Days Onsite]@row <= 21), "1521", IF(AND([Days Onsite]@row >= 22, [Days Onsite]@row <= 30), "2230", 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 07 not the 714 and if something is 14 then it is in the 814 not the 1421 etc...
Can be tweaked back :)
Good luck
(you can hide the Days Onsite column...)
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!