CSE Forumla to calculate no of months for which stock will suffice

[fastnswift]

Hi, I am new to his forum and to CSE functions... I have in row 20 the no of units in stock at the end of each month. In another row, I have the demand for units every month. Is it possible for excel to automatically calculate the no of months for which the stock in hand will suffice? Many thanks for all your help in advance

 

[gaz_chops]

Welcome to the forum.

Add an example of your data, see my signature for ways of doing so.

 

[fastnswift]

opening balance
Units Purchased
Units available in stock
Demand
Closing balance

<colgroup><col style="mso-width-source:userset;mso-width-alt:5083;width:104pt" width="139"> <col style="width:48pt" span="14" width="64"> </colgroup><tbody>
[TD="width: 139"][/TD]
[TD="width: 64"][/TD]
[TD="class: xl63, width: 64, align: right"]Jan-15[/TD]
[TD="class: xl63, width: 64, align: right"]Feb-15[/TD]
[TD="class: xl63, width: 64, align: right"]Mar-15[/TD]
[TD="class: xl63, width: 64, align: right"]Apr-15[/TD]
[TD="class: xl63, width: 64, align: right"]May-15[/TD]
[TD="class: xl63, width: 64, align: right"]Jun-15[/TD]
[TD="class: xl63, width: 64, align: right"]Jul-15[/TD]
[TD="class: xl63, width: 64, align: right"]Aug-15[/TD]
[TD="class: xl63, width: 64, align: right"]Sep-15[/TD]
[TD="class: xl63, width: 64, align: right"]Oct-15[/TD]
[TD="class: xl63, width: 64, align: right"]Nov-15[/TD]
[TD="class: xl63, width: 64, align: right"]Dec-15[/TD]
[TD="class: xl63, width: 64, align: right"]Jan-16[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]17[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]14[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]31[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]21[/TD]

</tbody>

 

[fastnswift]

Hi gaz_chops,

Thanks for your revert... The above is a snapshot of my data. I just need to calculate the number of months for which my stock in hand at the beginning of the month would suffice. For E.g. the output I am looking for is 15 units in Jan (after purchases) would suffice for 1 month of demand. 35 units of June 15 would suffice for 2 months of demand, etc... This is an almost perpetual sheet running into a number of months with the demand numbers fluctuating frequently. Hence, the need to automate it.

Thanks for your help