Counting the columns until cumulative sum reach a value

[SamGlasto]

Hi!
I need your help!, I am looking a way to know how many weeks ( range from C:L) my current stock (Column B) will cover my future weeks needs. On the column A. I am trying with MATCH,INDEX,SUBTOTAL COlumn functions, however I still don't get it correct.

Any guidance will be appreciate

 

[bosco_yip]

Try,

In A2, array formula (ctrl+Shift+Enter) copied down :

=MID(INDEX($C$1:$M$1,MIN(11,MATCH(B2,SUMIF(OFFSET(C2,,,,COLUMN(A:K)),">0")+1)+1)),7,9)

 

[SamGlasto]

Thanks Bosco!, I had to make some twitch however it works perfectly,

Just to keep this if anyone else need this info in the future.... i found also this other way to have the info :

=IFERROR(MATCH(TRUE,MMULT((0+C2:L2),--(TRANSPOSE(COLUMN(C2:L2))<=COLUMN(C2:L2)))>B2,0)-1,COLUMNS(C2:L2))

I still dont understand the formula ... however it works...




Again thanks for your support!