slopermaster
New Member
Hello,
I have the following problem that I am unable to solve:
I have a document (see attachment) which lists the number of open orders per calendar week. Also, the capacity (how many orders can be processed) is listed per calendar week.
I now need to calculate the amount of weeks needed until the open orders have been completely processed, depending on the processing capacity of future calendar weeks.
Until now, in order to calculate how many weeks are needed to process the 50,000 open orders from Week 1, I would sum up the processing capacity for Week 1 and onwards, until that sum is less or equal to 50,000. Then, I count the amount of cells I used. Finally, I subtract the difference and divide this difference by the capacity of the week that would "put me over the threshhold" of open orders. (see example, hard to explain)
EXAMPLE WITH WEEK 1:
Open orders:
50,000
Summing capacity of weeks until <= open orders:
Week 1: 8000 + Week 2: 7500 + Week 3: 9000 + Week 4: 9800 + Week 5: 7000 = 41,300 over 5 weeks
Determining the remainder:
50,000 - 41,300 = 8,700
8,700 divided by capacity of week 5 +1 (week 6) => 8,700 / 11,000 ~=~ 0.8
Therefore, it would take a total of 5.8 weeks until the open orders of 50,000 are completely processed.
Ideally, I would have formula that computes this value for each calendar week automatically, but I can't think of one that works...
EDIT: Unfortunately I need a solution without using a macro / writing VBA... otherwise I would have done it this way.
Thanks in advance..
I have the following problem that I am unable to solve:
I have a document (see attachment) which lists the number of open orders per calendar week. Also, the capacity (how many orders can be processed) is listed per calendar week.
I now need to calculate the amount of weeks needed until the open orders have been completely processed, depending on the processing capacity of future calendar weeks.
Until now, in order to calculate how many weeks are needed to process the 50,000 open orders from Week 1, I would sum up the processing capacity for Week 1 and onwards, until that sum is less or equal to 50,000. Then, I count the amount of cells I used. Finally, I subtract the difference and divide this difference by the capacity of the week that would "put me over the threshhold" of open orders. (see example, hard to explain)
EXAMPLE WITH WEEK 1:
Open orders:
50,000
Summing capacity of weeks until <= open orders:
Week 1: 8000 + Week 2: 7500 + Week 3: 9000 + Week 4: 9800 + Week 5: 7000 = 41,300 over 5 weeks
Determining the remainder:
50,000 - 41,300 = 8,700
8,700 divided by capacity of week 5 +1 (week 6) => 8,700 / 11,000 ~=~ 0.8
Therefore, it would take a total of 5.8 weeks until the open orders of 50,000 are completely processed.
Ideally, I would have formula that computes this value for each calendar week automatically, but I can't think of one that works...
EDIT: Unfortunately I need a solution without using a macro / writing VBA... otherwise I would have done it this way.
Thanks in advance..
Attachments
Last edited: