excelhelp243
New Member
- Joined
- Feb 10, 2015
- Messages
- 5
I have multiple products with days until next shipment, inventory, and the start of a formula but I'm stuck. I don't know what to do to subtract the outbound inventory from a FIFO standpoint and keep my days in inventory calculation correct.
To best summarize where I'm at I have written some nested IF functions
The first one is if I have more inventory since the last load (meaning that my inventory today is greater than the last date of inventory) Then I add the last number of days in inventory to the new number of days.
The next portion is if inventory remains the same. Then I add the number of days since the last shipment and the cumulative days in inventory so far.
The next portion is if inventory drops to zero. Then we no longer have any days in inventory so it is 0 automatically.
The last portion is what I am struggling with. I need the new portion of inbound inventory to subtract from the oldest amount of inventory and then subtract the number of days the oldest was in inventory from my number of days in inventory.
My current formula is IF(CurrentRunningInventory>LastRunningInventory,LastDaysinInventory+CurrentDaysinInventroy,IF(CurrentRunningInventory=LastRunningInventory,LastDaysinInventory+RunningDaysinInventory,IF(CurrentRunningInventory=0,0,?)))
best example is below
Widget 1 10 days 25 widgets 10 total days in inventory
Widget 1 5 days 30 widgets 15 total days in inventory
Widget 1 20 days 10 widgets 35 total days in inventory
Widget 1 5 days -15 widgets 40 days in inventory
Widget 1 5 days -40 widgets 30 days in inventory
The italics part are what my formula cannot return correctly.
To best summarize where I'm at I have written some nested IF functions
The first one is if I have more inventory since the last load (meaning that my inventory today is greater than the last date of inventory) Then I add the last number of days in inventory to the new number of days.
The next portion is if inventory remains the same. Then I add the number of days since the last shipment and the cumulative days in inventory so far.
The next portion is if inventory drops to zero. Then we no longer have any days in inventory so it is 0 automatically.
The last portion is what I am struggling with. I need the new portion of inbound inventory to subtract from the oldest amount of inventory and then subtract the number of days the oldest was in inventory from my number of days in inventory.
My current formula is IF(CurrentRunningInventory>LastRunningInventory,LastDaysinInventory+CurrentDaysinInventroy,IF(CurrentRunningInventory=LastRunningInventory,LastDaysinInventory+RunningDaysinInventory,IF(CurrentRunningInventory=0,0,?)))
best example is below
Widget 1 10 days 25 widgets 10 total days in inventory
Widget 1 5 days 30 widgets 15 total days in inventory
Widget 1 20 days 10 widgets 35 total days in inventory
Widget 1 5 days -15 widgets 40 days in inventory
Widget 1 5 days -40 widgets 30 days in inventory
The italics part are what my formula cannot return correctly.
