How to set date by sum value?

[qdone]

Hi,

I'm seeking for help for my work because I have an urgent problem which I cannot solve myself:

I have a column which contains the product quantity for every order and I have to set a date for each of them (usually Mondays) but there is a criteria what says you cannot put more than 50 products (order QTY) per week. Is there any formula I can use to automatically change the date after the sum reached or about to reach 50? I used to do this manually but it's about getting very time consuming.

For example:



So you can see at A3 the "QTY" reached 47 so at B4 the date should be changed for next week. Then at A8 it reaches 50 again then the date must be changed in B9 etc.

Hope that someone will know the answer..

Thanks for the help in advance!

 

[p45cal]

Attach a workbook! You'll get a faster better answer (we'll know whether those are real Excel dates or not and the answer you'll get will be sure to work).
What version of Excel are you using?

B3:
=IF(SUMIF(B$2:B2,B2,A$2:A2)+A3>50,B2+7,B2)
Real date in B2

 

[qdone]

Thanks for the help, it works! <3

 

[Peter Bartholomew]

This is a very different answer that is specific to the latest version of Excel 365.

The formula scans the order quantity twice, the first pass accumulates the weekly quantities (flagging the first entry of each week with a minus sign), whilst the second uses negative signs to increment the date.

= LET(result, SCAN(0,qty, LAMBDA(p,q, IF(ABS(p)+q<=50,ABS(p)+q,-q))),
      SCAN(refDate, result, LAMBDA(date,r, IF(r<0, date+7, date))))

 

[Peter Bartholomew]

Just to check, I calculations 10,000 rows, outputting both cumulative quantities up to 500 and dates. The calculations took ~20μs. By comparison, the non-array version took ~4,000μs. Increasing the row count to 20,000 rows, the respective figures were 40μs and 16,000μs respectively.

Apart from any inherent speed gains derived from using array formulas, SCAN avoids the use of an O(n²) formula.