Need help with creating aging buckets.

Iris Fuentes

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.

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...)

Debbie Sawyer

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 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...)