Hello,
I hope someone helps me with Aging the inventory based on usage. I have the
usage data and am trying to create buckets of 0-30Days, 31-60Days, >60Days based
on the date used.
For example, if part#C4871A is used in last 30 days, it should be in 0-30Days.
Even if this item is used in last 60 days, the aging should be still set as
0-30Days. Can you please help me with the formula for this type of aging?
Thanks
PKJ
Re: Aging Inventory
So, if the item was used in the last 30 days, the bucket is 0-30days
and if the item was uned in the last 60 days, the bucket is 0-30 days?
I am not sure I understand your question.
what about =IF(TODAY()-"Last used date" <= 30, "0-30Days",IF(TODAY()-"Last used date" <= 60,"31-60Days",">60Days")))
Re: Aging Inventory
Thanks for your help. Here is the table. The Age below should look at both Part#, Days and bucket the categories accordingly. Though C4871A has 282, 254 and 8 days, the age is still 0-30Days(because the latest usage is less than 30 days old). Likewise, C4872A has both 313 and 36 days, the age is 31-60days and so on......hope you got me now....thank you very much for your help.
[TABLE="width: 513"]
Part#
[/td]Used Date
[/td]Days
[/td]Age
[/td]C4871A
[/td]2/1/2011
[/td]282
[/td]0-30Days
[/td]C4871A
[/td]3/1/2011
[/td]254
[/td]0-30Days
[/td]C4872A
[/td]10/5/2011
[/td]36
[/td]31-60Days
[/td]C4872A
[/td]1/1/2011
[/td]313
[/td]31-60Days
[/td]C4873A
[/td]11/5/2011
[/td]5
[/td]0-30Days
[/td]C4873A
[/td]5/1/2011
[/td]193
[/td]0-30Days
[/td]C4874A
[/td]10/4/2011
[/td]37
[/td]31-60Days
[/td]C4871A
[/td]11/2/2011
[/td]8
[/td]0-30Days
[/td]
[/TABLE]
Re: Aging Inventory
I put your values in a spreadsheet, starting in A1. Here is the formula for cell D1:
{=IF(SMALL(IF(A2=$A$2:$A$1000,$C$2:$C$1000,10000),1)<=30,"0-30Days",IF(SMALL(IF(A2=$A$2:$A$1000,$C$2:$C$1000,10000),1)<=60,"31-60Days",">60Days"))}
This is an array function. To enter it, enter everything but the brackets. Then, hold ctrl+shift while pressing enter to exit the cell. The formula creates an array, and then find the smallest value. I set the alternative value to 10000, which should be OK given your buckets.
Re: Aging Inventory
Thank you very much. This is very helpful....
Don’t have an account yet? Register yourself now and be a part of our community!
