# 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

• 9.3 KB Views: 5
Last edited:

#### Excel Wizard

##### Active Member
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

• 11.2 KB Views: 2

#### bosco_yip

##### Excel Ninja
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)

#### slopermaster

##### New Member
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.

#### slopermaster

##### New Member
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)

View attachment 77497

Thank you, this is great!