Variable sum formula

[DanKokoro]

Hi all,

Didn't really know what to call this thread, as i don't even know what it is i am after. But i will do my best to explain.

I work in FMCG and i am after a formula that will tell me how long my stock on hand of a particular product will last based on forecasted sales.

I have a data table that has forecasted sales by item by month.

In column A starting at A3 i have my products listed, e.g. A3 = "Cheese", A4 = "*******s", A5 = "Biscuits".

In row 2 starting at B2 i have the months of the year, e.g. B2 = "January", B3 = "February" etc.

In cell A1 and A2 are my variables. A1 is the product name, and A2 is the stock on hand.

What i am after is a formula that will look up the product name, and then sum the forecasted sales, starting with January, until it exceeds the stock on hand (A2), and then return the month in which that occurs.

So for example. A1 = "*******s" and A2 = 100 (units).
Forecast for *******s is January=50, February=30, March =30, April =20.

I want the formula to return "March", because according to my forecasts, it is in March that i will run out of *******s (50+30+30 exceeds 100)..

This would make my job 100 times easier so i'd really appreciate some help on this one!! thanks very much in advance folks!

I apologise if this could have been explained better...

 

[T. Valko]

Hi all,

Didn't really know what to call this thread, as i don't even know what it is i am after. But i will do my best to explain.

I work in FMCG and i am after a formula that will tell me how long my stock on hand of a particular product will last based on forecasted sales.

I have a data table that has forecasted sales by item by month.

In column A starting at A3 i have my products listed, e.g. A3 = "Cheese", A4 = "*******s", A5 = "Biscuits".

In row 2 starting at B2 i have the months of the year, e.g. B2 = "January", B3 = "February" etc.

In cell A1 and A2 are my variables. A1 is the product name, and A2 is the stock on hand.

What i am after is a formula that will look up the product name, and then sum the forecasted sales, starting with January, until it exceeds the stock on hand (A2), and then return the month in which that occurs.

So for example. A1 = "*******s" and A2 = 100 (units).
Forecast for *******s is January=50, February=30, March =30, April =20.

I want the formula to return "March", because according to my forecasts, it is in March that i will run out of *******s (50+30+30 exceeds 100)..

This would make my job 100 times easier so i'd really appreciate some help on this one!! thanks very much in advance folks!

I apologise if this could have been explained better...

Try this...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Prod3	_	_	_	_	_	_	_	_	_	_	_	_
2	455	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
3	Prod1	92	77	50	75	44	21	10	16	42	72	14	49
4	Prod2	99	95	84	3	93	85	85	46	56	67	71	82
5	Prod3	87	83	85	56	18	50	66	47	81	36	69	78
Sheet1

This array formula**:

=INDEX(B2:M2,MATCH(TRUE,SUBTOTAL(9,OFFSET(B3,MATCH(A1,A3:A5,0)-1,,,COLUMN(B3:M3)-COLUMN(B3)+1))>=A2,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

For Prod3, the current inventory of 455 will run out in Aug.

 

[DanKokoro]

that probably would have only taken me 2 lifetimes to figure out by myself.. worked perfectly.. thanks so much!

 

[tusharm]

You have already received a solution but here's a UI tip. You can dramatically improve the UI and improve the usability of your worksheet by using columns B and C to store the inventory on hand and the calculated month when stock will run out. Now, you can see the results for *all* your products at the same time!

Hi all,

Didn't really know what to call this thread, as i don't even know what it is i am after. But i will do my best to explain.

I work in FMCG and i am after a formula that will tell me how long my stock on hand of a particular product will last based on forecasted sales.

I have a data table that has forecasted sales by item by month.

In column A starting at A3 i have my products listed, e.g. A3 = "Cheese", A4 = "*******s", A5 = "Biscuits".

In row 2 starting at B2 i have the months of the year, e.g. B2 = "January", B3 = "February" etc.

In cell A1 and A2 are my variables. A1 is the product name, and A2 is the stock on hand.

What i am after is a formula that will look up the product name, and then sum the forecasted sales, starting with January, until it exceeds the stock on hand (A2), and then return the month in which that occurs.

So for example. A1 = "*******s" and A2 = 100 (units).
Forecast for *******s is January=50, February=30, March =30, April =20.

I want the formula to return "March", because according to my forecasts, it is in March that i will run out of *******s (50+30+30 exceeds 100)..

This would make my job 100 times easier so i'd really appreciate some help on this one!! thanks very much in advance folks!

I apologise if this could have been explained better...

 

[T. Valko]

that probably would have only taken me 2 lifetimes to figure out by myself.. worked perfectly.. thanks so much!

You're welcome. Thanks for the feedback!