I'm trying to add qty to Inventory (B5:G7) so that Supply meets Demand by first looking at the Storage with the lowest fruit count and adding the qty based on the lot size.
Demand is manual input
Supply is Sum(B5:B7), Sum(C5:C7)+B3 and on (cumulative)
Total is Supply - Demand
Storage table is used only to find the fruit with the lowest count. Then adding the qty based on the lot size
Apple is 4, 8, 12, 16... Orange is 5, 10, 15, 20... and so on
The result should look like this:
Again the goal is to add qty so that Total is equal or above 0, other way to think about this is to match or exceed demand
I tried to first rank the fruits in Storage table with something like
But not sure how I can add based on the lot size...
I'm looking for a solution using formula, VBA, Solver or any other combination of excel features
Or let me know if this is not possible in excel...
Thank you
Cross posts:
https://www.mrexcel.com/forum/excel-questions/1101321-adding-inventory-based-lot-size.html
https://chandoo.org/forum/threads/adding-inventory-based-on-lot-size.41929/
Demand is manual input
Supply is Sum(B5:B7), Sum(C5:C7)+B3 and on (cumulative)
Total is Supply - Demand
Storage table is used only to find the fruit with the lowest count. Then adding the qty based on the lot size
Apple is 4, 8, 12, 16... Orange is 5, 10, 15, 20... and so on
The result should look like this:
Again the goal is to add qty so that Total is equal or above 0, other way to think about this is to match or exceed demand
I tried to first rank the fruits in Storage table with something like
Code:
[COLOR=#333333]IFERROR(INDEX(A15:A17,MATCH(MIN(C15:C17),C15:C17,0)),"")[/COLOR]But not sure how I can add based on the lot size...
I'm looking for a solution using formula, VBA, Solver or any other combination of excel features
Or let me know if this is not possible in excel...
Thank you
Cross posts:
https://www.mrexcel.com/forum/excel-questions/1101321-adding-inventory-based-lot-size.html
https://chandoo.org/forum/threads/adding-inventory-based-on-lot-size.41929/
Last edited by a moderator: