Forward Cover Calculation For Horizontal Data

Explanation of manual results

Week 1 Closing Stock 500
Week 2 ,3,4 Sales all 150 each; so Stock Cover is 3 & something weeks, balance is 500-450=50 which divided by Week 5 Sales (250) which is 0.2, so total 3.2 weeks

Week 2 Closing Stock 500
Week 3 & 4 Sales 300 but Week 5 Sales is 250; ; so Stock Cover is 2 & something weeks, balance is 500-300=200 which divided by Week 5 Sales (250) which is 0.8, so total 2.8 weeks


etc etc

Try

in B4 ("helper" row)

=MATCH(TRUE,SUBTOTAL(9,OFFSET(C2:$K2,,,,COLUMN(C2:$K2)-COLUMN(C2)+1))>B3,0)-1

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy across

in B5

=B4+(B3-SUM(OFFSET(C2,,,,B4)))/OFFSET(B2,,B4+1)

John, that's spot on - thank you! Had tried the same but think came unstuck with the control-shift-enter and lost confidence in the ability of OFFSET & COLUMN to replace OFFSET & ROW for Horizontal data.

I did by the way come up with this little one to deal with the problem if i couldn't find a solution - benefits of this approach is it doesn't use a "volatile" function, whilst it is limited in that it gets capped at 7 days.
'=IF(B3-SUM(C2:INDEX(C2:J2,7))>=0,">7 Days",IF(B3-SUM(C2:INDEX(C2:J2,6))>=0,6+(B3-SUM(C2:INDEX(C2:J2,6)))/INDEX(C2:J2,6+1),IF(B3-SUM(C2:INDEX(C2:J2,5))>=0,5+(B3-SUM(C2:INDEX(C2:J2,5)))/INDEX(C2:J2,5+1),IF(B3-SUM(C2:INDEX(C2:J2,4))>=0,4+(B3-SUM(C2:INDEX(C2:J2,4)))/INDEX(C2:J2,4+1),IF(B3-SUM(C2:INDEX(C2:J2,3))>=0,3+(B3-SUM(C2:INDEX(C2:J2,3)))/INDEX(C2:J2,3+1),IF(B3-SUM(C2:INDEX(C2:J2,2))>=0,2+(B3-SUM(C2:INDEX(C2:J2,2)))/INDEX(C2:J2,2+1),IF(B3-SUM(C2:INDEX(C2:J2,1))>=0,1,B3/C2)))))))

Where Sales Starts in B2, and stock in b3 (sorry new to this so couldnt upload a file)