• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Determining after summation of how many cells a certain value is reached?

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..
 

Attachments

  • OrderBacklog.xlsx
    9.3 KB · Views: 5
Last edited:
Pleeas try
=MATCH(B2,MMULT(--B3:M3,--(COLUMN(B3:M3)>=TRANSPOSE(COLUMN(B3:M3)))))-LOOKUP(0,(MMULT(--B3:M3,--(COLUMN(B3:M3)>=TRANSPOSE(COLUMN(B3:M3))))-B2)/C3:N3)

Ctrl+Shift+Enter

or MS365
=LET(w,B3:M3,o,B2,c,COLUMN(w),a,MMULT(--w,--(c>=TRANSPOSE(c))),MATCH(B2,a)-LOOKUP(0,(a-o)/C3:N3))
 

Attachments

  • OrderBacklog.xlsx
    11.2 KB · Views: 3
Or,

In B4, formula copied across right to M4:

=MATCH(B2,INDEX(SUMIF(OFFSET(B3,,,,COLUMN($A1:$U1)),"<>"),0))-LOOKUP(0,(SUMIF(OFFSET(B3,,,,COLUMN($A1:$U1)),"<>")-B2)/C3:N3)

77497
 
Pleeas try
=MATCH(B2,MMULT(--B3:M3,--(COLUMN(B3:M3)>=TRANSPOSE(COLUMN(B3:M3)))))-LOOKUP(0,(MMULT(--B3:M3,--(COLUMN(B3:M3)>=TRANSPOSE(COLUMN(B3:M3))))-B2)/C3:N3)

Ctrl+Shift+Enter

or MS365
=LET(w,B3:M3,o,B2,c,COLUMN(w),a,MMULT(--w,--(c>=TRANSPOSE(c))),MATCH(B2,a)-LOOKUP(0,(a-o)/C3:N3))


Fantastic, thank you very much.
 
Back
Top