Hi,
I am new to this forum but hoping someone might be able to help me with a question that I feel like there should be an easy answer to, but I can't seem to come up with it. Here's what I've got:
I have a certain number of items that have already been ordered for the next 9 weeks (row 2). I also have a certain capacity to produce items each week (row 3). I always want to use all of my production capacity, so if "Items Ordered" is less than "Items Production Capacity" I would like to pull items from future weeks. The amount that I need to pull from future weeks is shown in row 3.
I am trying to figure out an equation that will account for pulling from multiple weeks if pulling from the next week does not completely reach capacity of 110. For example below, in Week 2 we had orders for 42 (C2) items but 30 of those orders were pulled into the previous week and made (B4) leaving only 12 items to be made (C5). This means to reach my capacity of 110 I need to pull 98 items (C4) from the next week. There are only 97 items to pull from the next week (D2) so I would like it to pull one additional item from two weeks out (E2). I have been able to figure this out with MAX / MIN formulas or if statements when it only needs to pull from the next week, but I can't figure out how to make it smart enough to know if it needs to pull from more than 1 week out which starts to happen once it gains begin to compound.
Any help would be much appreciated!
Thanks,
Isaac
PS - See attached Image for chart
Excel.png
Bookmarks