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
Check out the Formula Handbook template!