Stock & sales calculation

[Sigh]

I cells B1-M1 I have the months of the year, In A2 I have the current stock figure, in B2-M2 are the sales figures for each month in the previous year. In N2 I would like a calculation that subtracts the sales from the stock figure and reports the month in which there will be no stock left, is this possible in a formula?

 

[Stiuart_W]

=INT((A2-SUM(B2:M2))/AVERAGE(B2:M2))+1

This assumes stock is not zero at start of new year. Reports the month as a number

 

[Sigh]

Thanks for your reply. As an example I entered 11 as the stock in A2 and 1 under each of the months, I would expect 11 to be the answer but I get zero?

 

[Stiuart_W]

My mistake. Mis-read the OP

=MOD(INT(A2/AVERAGE(B2:M2)),12)

 

[Sigh]

That's perfect, thank you for your help!

 

[Sigh]

I spoke too soon! Using the example I quoted earlier, if one of the months is zero the answer is also zero, which is wrong. Also, if I make the stock figure 500 and keep the months at 1 each I get the answer of 8.

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	6000	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	Sales	1,000	1400	1600	1200	1200	2000	2000	2000	2000	2000	2000	2000	May
3	Sales	2000	1800	2400	1200	1200	1200	1200	1200	1200	1200	1200	1200	March
4
4a

Cell Formulas
Range	Formula
N2	{=TEXT(29*MIN(IF(SUBTOTAL(9,OFFSET($B2,0,0,1,COLUMN($B2:$M2)-COLUMN(B2)+1))>=$A$1, COLUMN($B2:$M2)-COLUMN(B2)+1)),"mmmm")}
N3	{=TEXT(29*MIN(IF(SUBTOTAL(9,OFFSET($B3,0,0,1,COLUMN($B3:$M3)-COLUMN(B3)+1))>=$A$1, COLUMN($B3:$M3)-COLUMN(B3)+1)),"mmmm")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Sigh]

Wow, thank you so much! My sales run April 16 to March 17, how do I adjust the formula to suit this please?

 

[Dave Patton]

The major part of the formula yields the position when criteria is met such as 3

MIN(IF(SUBTOTAL(9,OFFSET($B2,0,0,1,COLUMN($B2:$M2)-COLUMN(B2)+1))>=$A$1, COLUMN($B2:$M2)-COLUMN(B2)+1))

Build a lookup table that yields the text that you require instead of

=TEXT(29*num,"mmmm")} which converts a number like 3 to March

 

[Sigh]

Okay, thanks again for your help with this.