when is my next need date

[stran009]

hello,
Can someone please help me with a formula to see when is my next need date will be?
ex: 1st row I have 50 pieces minus each month quantities take away: 20, 20, 10, 10, 0

I need a formula that will calculate how far my stock will cover and automatically put in a date
[TABLE="width: 691"]
<tbody>[TR]
[TD]product name[/TD]
[TD]stock qty[/TD]
[TD]next need date[/TD]
[TD][/TD]
[TD="align: right"]17-Nov[/TD]
[TD="align: right"]17-Dec[/TD]
[TD="align: right"]18-Jan[/TD]
[TD="align: right"]18-Feb[/TD]
[TD="align: right"]18-Mar[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]50[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]100[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]150[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD="align: right"]200[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD="align: right"]250[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col span="5"></colgroup>[/TABLE]

 

[Dr. Demento]

I'm sure there's a slicker way to do this, but here's what I came up with.

Create five helper columns (K2:O2; one column for each inventory date -- 17-Nov, 17-Dec, etc.). Paste this formula into the first helper cell and then copy/paste into the remaining helper range (K2:O6):

=IF($B2<=SUM($E2:E2),E$1,"")

where
$B2 is the Stock Qty,
E$1 is the inventory date, and
SUM($E2:E2) sums the quantity used as of the inventory date.
This provides the date when the inventory is at or less than 0

In Next Need Date (C2:C6) column, use this formula:

=IF(SUMPRODUCT(--(K2:O2<>""))=0,"",MIN(K2:O2))

where
SUMPRODUCT(--(K2:O2<>""))=0 tests if all cells in the K2:O2 range are blank; if there's a non-blank entry in K2:O2, then the first date where Stock Qty - sum of Inventory Used <= 0 is shown (MIN(K2:O2)). Source

From there, you can hide the helper columns.

hth

 

[stran009]

I'm sure there's a slicker way to do this, but here's what I came up with.

Create five helper columns (K2:O2; one column for each inventory date -- 17-Nov, 17-Dec, etc.). Paste this formula into the first helper cell and then copy/paste into the remaining helper range (K2:O6):

=IF($B2<=SUM($E2:E2),E$1,"")

where
$B2 is the Stock Qty,
E$1 is the inventory date, and
SUM($E2:E2) sums the quantity used as of the inventory date.
This provides the date when the inventory is at or less than 0

In Next Need Date (C2:C6) column, use this formula:

=IF(SUMPRODUCT(--(K2:O2<>""))=0,"",MIN(K2:O2))

where
SUMPRODUCT(--(K2:O2<>""))=0 tests if all cells in the K2:O2 range are blank; if there's a non-blank entry in K2:O2, then the first date where Stock Qty - sum of Inventory Used <= 0 is shown (MIN(K2:O2)). Source

From there, you can hide the helper columns.

hth

Thank for your input Dr Demento.
maybe I am misunderstanding, but should there also be a formula to subtract my qty each month taken from the total stock qty. then it should show my next need date qty. From my 1st post. the next need date would be march 2018, because stock qty has been depleted from the previous months.
thanks again for the help

 

[Dr. Demento]

stra009,

You're welcome. Glad I could help('ish).

Regarding your concerns, I made some assumptions that might have caused me to solve the problem the wrong way.
1) I assumed that E1:I6 was how much product was USED each month, not how much was remaining. Given that assumption, Product A runs out on 18-Jan, Products B & C do not run out in the timeframe shown, and Products D & E both run out 18-Feb. This also presumes that each product is ordered and used independent, therefore each product has different dates when more will be needed.
2) In the setup I provided, the Stock Qty will need to be kept as a running total of product received (each time a new shipment arrives, it would have to be ADDED on to the previous amounts received and NOT have the past quantities overwritten with the latest qty received. To do this, I would suggest tracking/logging all shipments received on a separate worksheet that tracks Product Name | Qty Received | Date Received for each individual product shipment receipt. From there, the Stock Qty can be automatically updated using the formula below in B2:B6

=SUMIF(Sheet2!$A:$A,Sheet1!A2,Sheet2!$B:$B)

If that doesn't work for you, I will require a better understanding of the data you presented and the expected/desired results.

Cheers.

 

[stran009]

stra009,

You're welcome. Glad I could help('ish).

Regarding your concerns, I made some assumptions that might have caused me to solve the problem the wrong way.
1) I assumed that E1:I6 was how much product was USED each month, not how much was remaining. Given that assumption, Product A runs out on 18-Jan, Products B & C do not run out in the timeframe shown, and Products D & E both run out 18-Feb. This also presumes that each product is ordered and used independent, therefore each product has different dates when more will be needed.
2) In the setup I provided, the Stock Qty will need to be kept as a running total of product received (each time a new shipment arrives, it would have to be ADDED on to the previous amounts received and NOT have the past quantities overwritten with the latest qty received. To do this, I would suggest tracking/logging all shipments received on a separate worksheet that tracks Product Name | Qty Received | Date Received for each individual product shipment receipt. From there, the Stock Qty can be automatically updated using the formula below in B2:B6

=SUMIF(Sheet2!$A:$A,Sheet1!A2,Sheet2!$B:$B)

If that doesn't work for you, I will require a better understanding of the data you presented and the expected/desired results.

Cheers.

Thank you again Dr. Demento

I will try to play around with this and advise. I am totally new to this excel thing and only know the mere basics. Is there is site or some course out there to help expand my knowledge?

 

[Dr. Demento]

As far as learning, I would suggest two approaches: an "Excel for Dummies" or some such basic book is a good idea to begin to understand the basic concepts (conditional formatting, formula types & syntax, or pivot tables, etc). After that, Google (and the multiple forums that it will bring up) is your best resource, provided you can clearly and succinctly define your question (which is why I suggest the book - gives you a basic understanding of concepts, capabilities).

For example, when I originally attempted to answer your question, I looked for an INDEX/MATCH solution, where the MATCH was not the typical match but would instead use a conditional inequality (identify the column where the sum of the used inventory was larger or equal to the stock quantity). Because I couldn't define/phrase my approach, I couldn't find if anyone had posted a solution along these line and therefore had to switch my approach. I came upon the SUMPRODUCT term because I was looking for a formula that would test if an entire range was blank instead of just a single cell. Finding Excel Enlightenment comes down to being familiar with the Excel terms and being able to put them together in a way that will identify past attempts to answer your same question.

After that, it's just time, trial, and error! Best wishes and keep coming back!!!!