I need to return the date of when my stock will run out based on the forecast.



LMCode Avail 1-Sep-08 15-Sep-08 29-Sep-08 13-Oct-08 27-Oct-08 10-Nov-08
24-Nov-08 8-Dec-08 22-Dec-08 5-Jan-09 19-Jan-09 2-Feb-09 16-Feb-09
NET001 64,000 36,000 0 0 48,000 0 0 60,000 0 0 0 48,000 0 48,000




Eg: I have 64,000 of Code "NET001" available. Based on the forecast, I need
to return the date when my stock will run out. In the above case 13-Oct-08.



I've used hlookup =HLOOKUP(I2,AC2:AO2,TRUE) but it only returns the value
48,000.



Please can someone help me?

Doesn't such a calculation necessarily include a use rate? That is, if you
have 64 000 of NET001 now and you never use any, then you'll have 64 000 of
NET001 for years to come. If you use 2 per day, you'll run out 32 000 days
from now. How can you make a forecast without that datum?

--- "Bam" wrote:
I need to return the date of when my stock will run out based on the forecast.

LMCode Avail 1-Sep-08 15-Sep-08 29-Sep-08 13-Oct-08 27-Oct-08 10-Nov-08
24-Nov-08 8-Dec-08 22-Dec-08 5-Jan-09 19-Jan-09 2-Feb-09 16-Feb-09
NET001 64,000 36,000 0 0 48,000 0 0 60,000 0 0 0 48,000 0 48,000

Eg: I have 64,000 of Code "NET001" available. Based on the forecast, I need
to return the date when my stock will run out. In the above case 13-Oct-08.

I've used hlookup =HLOOKUP(I2,AC2:AO2,TRUE) but it only returns the value
48,000.

The table didn't exactly come out clearly once posted. Apologies

The Available stock of NET001 is 64,000.

Period 1: 1-Sep-08 Usage = 36,000
Period 2: 15-Sep-08 Usage = 0
Period 3: 29-Sep-08 Usage = 0
Period 4: 13-Oct-08 Usage = 48,000
Period 5: 27-Oct-08 Usage = 0
etc..

So in theory I will run out of stock in the 4th Period - Or the 13th Oct.

LM Code Avail 01/09 15/09 29/09 13/10 27/10
NET001/3 64,000 36,000 0 0 48,000 0


Therefore I need to return the Date 13/10 - as in the example above - which
is hopefully easier to read.

"Bob Bridges" wrote:

Doesn't such a calculation necessarily include a use rate? That is, if you
have 64 000 of NET001 now and you never use any, then you'll have 64 000 of
NET001 for years to come. If you use 2 per day, you'll run out 32 000 days
from now. How can you make a forecast without that datum?

--- "Bam" wrote:
I need to return the date of when my stock will run out based on the forecast.

LMCode Avail 1-Sep-08 15-Sep-08 29-Sep-08 13-Oct-08 27-Oct-08 10-Nov-08
24-Nov-08 8-Dec-08 22-Dec-08 5-Jan-09 19-Jan-09 2-Feb-09 16-Feb-09
NET001 64,000 36,000 0 0 48,000 0 0 60,000 0 0 0 48,000 0 48,000

Eg: I have 64,000 of Code "NET001" available. Based on the forecast, I need
to return the date when my stock will run out. In the above case 13-Oct-08.

I've used hlookup =HLOOKUP(I2,AC2:AO2,TRUE) but it only returns the value
48,000.

A B C D E
F G H
1 LM Code Avail 09.jan 15.sep 29.sep 13.oct 27.oct
2 NET001/3 64 36 0 0 48 0
3 64 28 28 28 -20 -20 13.oct

in B3: =B2
in C3: =B3-C2, copy until G3
in H3: =INDEX(C1:G1,MATCH(MIN(C3:G3),C3:G3,0))
You get the required result in H3.

Regards,
Stefi

€žBam€� ezt Ã*rta:

The table didn't exactly come out clearly once posted. Apologies

The Available stock of NET001 is 64,000.

Period 1: 1-Sep-08 Usage = 36,000
Period 2: 15-Sep-08 Usage = 0
Period 3: 29-Sep-08 Usage = 0
Period 4: 13-Oct-08 Usage = 48,000
Period 5: 27-Oct-08 Usage = 0
etc..

So in theory I will run out of stock in the 4th Period - Or the 13th Oct.

LM Code Avail 01/09 15/09 29/09 13/10 27/10
NET001/3 64,000 36,000 0 0 48,000 0


Therefore I need to return the Date 13/10 - as in the example above - which
is hopefully easier to read.

"Bob Bridges" wrote:

Doesn't such a calculation necessarily include a use rate? That is, if you
have 64 000 of NET001 now and you never use any, then you'll have 64 000 of
NET001 for years to come. If you use 2 per day, you'll run out 32 000 days
from now. How can you make a forecast without that datum?

--- "Bam" wrote:
I need to return the date of when my stock will run out based on the forecast.

LMCode Avail 1-Sep-08 15-Sep-08 29-Sep-08 13-Oct-08 27-Oct-08 10-Nov-08
24-Nov-08 8-Dec-08 22-Dec-08 5-Jan-09 19-Jan-09 2-Feb-09 16-Feb-09
NET001 64,000 36,000 0 0 48,000 0 0 60,000 0 0 0 48,000 0 48,000

Eg: I have 64,000 of Code "NET001" available. Based on the forecast, I need
to return the date when my stock will run out. In the above case 13-Oct-08.

I've used hlookup =HLOOKUP(I2,AC2:AO2,TRUE) but it only returns the value
48,000.

A little refinement:
=IF(MIN(C3:G3)<=0,INDEX(C1:G1,MATCH(MIN(C3:G3),C3: G3,0)),"")
Stefi


€žStefi€� ezt Ã*rta:

A B C D E
F G H
1 LM Code Avail 09.jan 15.sep 29.sep 13.oct 27.oct
2 NET001/3 64 36 0 0 48 0
3 64 28 28 28 -20 -20 13.oct

in B3: =B2
in C3: =B3-C2, copy until G3
in H3: =INDEX(C1:G1,MATCH(MIN(C3:G3),C3:G3,0))
You get the required result in H3.

Regards,
Stefi

€žBam€� ezt Ã*rta:

The table didn't exactly come out clearly once posted. Apologies

The Available stock of NET001 is 64,000.

Period 1: 1-Sep-08 Usage = 36,000
Period 2: 15-Sep-08 Usage = 0
Period 3: 29-Sep-08 Usage = 0
Period 4: 13-Oct-08 Usage = 48,000
Period 5: 27-Oct-08 Usage = 0
etc..

So in theory I will run out of stock in the 4th Period - Or the 13th Oct.

LM Code Avail 01/09 15/09 29/09 13/10 27/10
NET001/3 64,000 36,000 0 0 48,000 0


Therefore I need to return the Date 13/10 - as in the example above - which
is hopefully easier to read.

"Bob Bridges" wrote:

Doesn't such a calculation necessarily include a use rate? That is, if you
have 64 000 of NET001 now and you never use any, then you'll have 64 000 of
NET001 for years to come. If you use 2 per day, you'll run out 32 000 days
from now. How can you make a forecast without that datum?

--- "Bam" wrote:
I need to return the date of when my stock will run out based on the forecast.

LMCode Avail 1-Sep-08 15-Sep-08 29-Sep-08 13-Oct-08 27-Oct-08 10-Nov-08
24-Nov-08 8-Dec-08 22-Dec-08 5-Jan-09 19-Jan-09 2-Feb-09 16-Feb-09
NET001 64,000 36,000 0 0 48,000 0 0 60,000 0 0 0 48,000 0 48,000

Eg: I have 64,000 of Code "NET001" available. Based on the forecast, I need
to return the date when my stock will run out. In the above case 13-Oct-08.

I've used hlookup =HLOOKUP(I2,AC2:AO2,TRUE) but it only returns the value
48,000.

Thanks Stefi.

I need to create the formula in a single cell without further cells being
used for calculations.
A B C D E
F G
1 LM Cover Avail 1-Sep 15-Sep 29-Sep 13-Oct
2 NET001 1/09/2008 64000 36000 0 0 48000

So i put the formula in B2.

Using =INDEX(D1:G1,MATCH(C2,D2:G2,1)) gets me the result 13-Oct.

Problem then is that once I start included the rest of the forecast numbers
the MATCH only looks for the closing matching number.

Eg:
H1 = 27-Oct H2 = 144,000
I1 = 10-Nov I2 = 96,000

Change the Avail Number (C2) to 150,000

Using =INDEX(D1:I1,MATCH(C2,D2:I2,1)) gets me the result 10-Nov.
which is wrong - It should return 27-Oct because it is within that period
that my stock will run out.

Any suggestions?
Thanks.


"Stefi" wrote:

A little refinement:
=IF(MIN(C3:G3)<=0,INDEX(C1:G1,MATCH(MIN(C3:G3),C3: G3,0)),"")
Stefi


€žStefi€� ezt Ã*rta:

A B C D E
F G H
1 LM Code Avail 09.jan 15.sep 29.sep 13.oct 27.oct
2 NET001/3 64 36 0 0 48 0
3 64 28 28 28 -20 -20 13.oct

in B3: =B2
in C3: =B3-C2, copy until G3
in H3: =INDEX(C1:G1,MATCH(MIN(C3:G3),C3:G3,0))
You get the required result in H3.

Regards,
Stefi

€žBam€� ezt Ã*rta:

The table didn't exactly come out clearly once posted. Apologies

The Available stock of NET001 is 64,000.

Period 1: 1-Sep-08 Usage = 36,000
Period 2: 15-Sep-08 Usage = 0
Period 3: 29-Sep-08 Usage = 0
Period 4: 13-Oct-08 Usage = 48,000
Period 5: 27-Oct-08 Usage = 0
etc..

So in theory I will run out of stock in the 4th Period - Or the 13th Oct.

LM Code Avail 01/09 15/09 29/09 13/10 27/10
NET001/3 64,000 36,000 0 0 48,000 0


Therefore I need to return the Date 13/10 - as in the example above - which
is hopefully easier to read.

"Bob Bridges" wrote:

Doesn't such a calculation necessarily include a use rate? That is, if you
have 64 000 of NET001 now and you never use any, then you'll have 64 000 of
NET001 for years to come. If you use 2 per day, you'll run out 32 000 days
from now. How can you make a forecast without that datum?

--- "Bam" wrote:
I need to return the date of when my stock will run out based on the forecast.

LMCode Avail 1-Sep-08 15-Sep-08 29-Sep-08 13-Oct-08 27-Oct-08 10-Nov-08
24-Nov-08 8-Dec-08 22-Dec-08 5-Jan-09 19-Jan-09 2-Feb-09 16-Feb-09
NET001 64,000 36,000 0 0 48,000 0 0 60,000 0 0 0 48,000 0 48,000

Eg: I have 64,000 of Code "NET001" available. Based on the forecast, I need
to return the date when my stock will run out. In the above case 13-Oct-08.

I've used hlookup =HLOOKUP(I2,AC2:AO2,TRUE) but it only returns the value
48,000.

Try this...

Dates in the range D1:J1
Usage amounts in the range D2:J2
Stock on hand in C2

Entered as an array** :

=INDEX(D1:J1,MATCH(TRUE,SUBTOTAL(9,OFFSET(D2:J2,,, ,COLUMN(D2:J2)-MIN(COLUMN(D2:J2))+1))=C2,0))

Format as date

A result of #N/A means you will not run out of stock.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Bam" wrote in message
...
Thanks Stefi.

I need to create the formula in a single cell without further cells being
used for calculations.
A B C D E
F G
1 LM Cover Avail 1-Sep 15-Sep 29-Sep 13-Oct
2 NET001 1/09/2008 64000 36000 0 0 48000

So i put the formula in B2.

Using =INDEX(D1:G1,MATCH(C2,D2:G2,1)) gets me the result 13-Oct.

Problem then is that once I start included the rest of the forecast
numbers
the MATCH only looks for the closing matching number.

Eg:
H1 = 27-Oct H2 = 144,000
I1 = 10-Nov I2 = 96,000

Change the Avail Number (C2) to 150,000

Using =INDEX(D1:I1,MATCH(C2,D2:I2,1)) gets me the result 10-Nov.
which is wrong - It should return 27-Oct because it is within that period
that my stock will run out.

Any suggestions?
Thanks.


"Stefi" wrote:

A little refinement:
=IF(MIN(C3:G3)<=0,INDEX(C1:G1,MATCH(MIN(C3:G3),C3: G3,0)),"")
Stefi


"Stefi" ezt írta:

A B C D E
F G H
1 LM Code Avail 09.jan 15.sep 29.sep 13.oct 27.oct
2 NET001/3 64 36 0 0 48 0
3 64 28 28 28 -20 -20 13.oct

in B3: =B2
in C3: =B3-C2, copy until G3
in H3: =INDEX(C1:G1,MATCH(MIN(C3:G3),C3:G3,0))
You get the required result in H3.

Regards,
Stefi

"Bam" ezt írta:

The table didn't exactly come out clearly once posted. Apologies

The Available stock of NET001 is 64,000.

Period 1: 1-Sep-08 Usage = 36,000
Period 2: 15-Sep-08 Usage = 0
Period 3: 29-Sep-08 Usage = 0
Period 4: 13-Oct-08 Usage = 48,000
Period 5: 27-Oct-08 Usage = 0
etc..

So in theory I will run out of stock in the 4th Period - Or the 13th
Oct.

LM Code Avail 01/09 15/09 29/09 13/10 27/10
NET001/3 64,000 36,000 0 0 48,000 0


Therefore I need to return the Date 13/10 - as in the example above -
which
is hopefully easier to read.

"Bob Bridges" wrote:

Doesn't such a calculation necessarily include a use rate? That
is, if you
have 64 000 of NET001 now and you never use any, then you'll have
64 000 of
NET001 for years to come. If you use 2 per day, you'll run out 32
000 days
from now. How can you make a forecast without that datum?

--- "Bam" wrote:
I need to return the date of when my stock will run out based on
the forecast.

LMCode Avail 1-Sep-08 15-Sep-08 29-Sep-08 13-Oct-08 27-Oct-08
10-Nov-08
24-Nov-08 8-Dec-08 22-Dec-08 5-Jan-09 19-Jan-09 2-Feb-09
16-Feb-09
NET001 64,000 36,000 0 0 48,000 0 0 60,000 0 0 0 48,000 0 48,000

Eg: I have 64,000 of Code "NET001" available. Based on the
forecast, I need
to return the date when my stock will run out. In the above case
13-Oct-08.

I've used hlookup =HLOOKUP(I2,AC2:AO2,TRUE) but it only returns
the value
48,000.

Thankyou Biff.

That seems to work!

"T. Valko" wrote:

Try this...

Dates in the range D1:J1
Usage amounts in the range D2:J2
Stock on hand in C2

Entered as an array** :

=INDEX(D1:J1,MATCH(TRUE,SUBTOTAL(9,OFFSET(D2:J2,,, ,COLUMN(D2:J2)-MIN(COLUMN(D2:J2))+1))=C2,0))

Format as date

A result of #N/A means you will not run out of stock.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Bam" wrote in message
...
Thanks Stefi.

I need to create the formula in a single cell without further cells being
used for calculations.
A B C D E
F G
1 LM Cover Avail 1-Sep 15-Sep 29-Sep 13-Oct
2 NET001 1/09/2008 64000 36000 0 0 48000

So i put the formula in B2.

Using =INDEX(D1:G1,MATCH(C2,D2:G2,1)) gets me the result 13-Oct.

Problem then is that once I start included the rest of the forecast
numbers
the MATCH only looks for the closing matching number.

Eg:
H1 = 27-Oct H2 = 144,000
I1 = 10-Nov I2 = 96,000

Change the Avail Number (C2) to 150,000

Using =INDEX(D1:I1,MATCH(C2,D2:I2,1)) gets me the result 10-Nov.
which is wrong - It should return 27-Oct because it is within that period
that my stock will run out.

Any suggestions?
Thanks.


"Stefi" wrote:

A little refinement:
=IF(MIN(C3:G3)<=0,INDEX(C1:G1,MATCH(MIN(C3:G3),C3: G3,0)),"")
Stefi


"Stefi" ezt Ã*rta:

A B C D E
F G H
1 LM Code Avail 09.jan 15.sep 29.sep 13.oct 27.oct
2 NET001/3 64 36 0 0 48 0
3 64 28 28 28 -20 -20 13.oct

in B3: =B2
in C3: =B3-C2, copy until G3
in H3: =INDEX(C1:G1,MATCH(MIN(C3:G3),C3:G3,0))
You get the required result in H3.

Regards,
Stefi

"Bam" ezt Ã*rta:

The table didn't exactly come out clearly once posted. Apologies

The Available stock of NET001 is 64,000.

Period 1: 1-Sep-08 Usage = 36,000
Period 2: 15-Sep-08 Usage = 0
Period 3: 29-Sep-08 Usage = 0
Period 4: 13-Oct-08 Usage = 48,000
Period 5: 27-Oct-08 Usage = 0
etc..

So in theory I will run out of stock in the 4th Period - Or the 13th
Oct.

LM Code Avail 01/09 15/09 29/09 13/10 27/10
NET001/3 64,000 36,000 0 0 48,000 0


Therefore I need to return the Date 13/10 - as in the example above -
which
is hopefully easier to read.

"Bob Bridges" wrote:

Doesn't such a calculation necessarily include a use rate? That
is, if you
have 64 000 of NET001 now and you never use any, then you'll have
64 000 of
NET001 for years to come. If you use 2 per day, you'll run out 32
000 days
from now. How can you make a forecast without that datum?

--- "Bam" wrote:
I need to return the date of when my stock will run out based on
the forecast.

LMCode Avail 1-Sep-08 15-Sep-08 29-Sep-08 13-Oct-08 27-Oct-08
10-Nov-08
24-Nov-08 8-Dec-08 22-Dec-08 5-Jan-09 19-Jan-09 2-Feb-09
16-Feb-09
NET001 64,000 36,000 0 0 48,000 0 0 60,000 0 0 0 48,000 0 48,000

Eg: I have 64,000 of Code "NET001" available. Based on the
forecast, I need
to return the date when my stock will run out. In the above case
13-Oct-08.

I've used hlookup =HLOOKUP(I2,AC2:AO2,TRUE) but it only returns
the value
48,000.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Bam" wrote in message
...
Thankyou Biff.

That seems to work!

"T. Valko" wrote:

Try this...

Dates in the range D1:J1
Usage amounts in the range D2:J2
Stock on hand in C2

Entered as an array** :

=INDEX(D1:J1,MATCH(TRUE,SUBTOTAL(9,OFFSET(D2:J2,,, ,COLUMN(D2:J2)-MIN(COLUMN(D2:J2))+1))=C2,0))

Format as date

A result of #N/A means you will not run out of stock.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Bam" wrote in message
...
Thanks Stefi.

I need to create the formula in a single cell without further cells
being
used for calculations.
A B C D
E
F G
1 LM Cover Avail 1-Sep 15-Sep 29-Sep 13-Oct
2 NET001 1/09/2008 64000 36000 0 0 48000

So i put the formula in B2.

Using =INDEX(D1:G1,MATCH(C2,D2:G2,1)) gets me the result 13-Oct.

Problem then is that once I start included the rest of the forecast
numbers
the MATCH only looks for the closing matching number.

Eg:
H1 = 27-Oct H2 = 144,000
I1 = 10-Nov I2 = 96,000

Change the Avail Number (C2) to 150,000

Using =INDEX(D1:I1,MATCH(C2,D2:I2,1)) gets me the result 10-Nov.
which is wrong - It should return 27-Oct because it is within that
period
that my stock will run out.

Any suggestions?
Thanks.


"Stefi" wrote:

A little refinement:
=IF(MIN(C3:G3)<=0,INDEX(C1:G1,MATCH(MIN(C3:G3),C3: G3,0)),"")
Stefi


"Stefi" ezt írta:

A B C D E
F G H
1 LM Code Avail 09.jan 15.sep 29.sep 13.oct 27.oct
2 NET001/3 64 36 0 0 48 0
3 64 28 28 28 -20 -20 13.oct

in B3: =B2
in C3: =B3-C2, copy until G3
in H3: =INDEX(C1:G1,MATCH(MIN(C3:G3),C3:G3,0))
You get the required result in H3.

Regards,
Stefi

"Bam" ezt írta:

The table didn't exactly come out clearly once posted. Apologies

The Available stock of NET001 is 64,000.

Period 1: 1-Sep-08 Usage = 36,000
Period 2: 15-Sep-08 Usage = 0
Period 3: 29-Sep-08 Usage = 0
Period 4: 13-Oct-08 Usage = 48,000
Period 5: 27-Oct-08 Usage = 0
etc..

So in theory I will run out of stock in the 4th Period - Or the
13th
Oct.

LM Code Avail 01/09 15/09 29/09 13/10 27/10
NET001/3 64,000 36,000 0 0 48,000 0


Therefore I need to return the Date 13/10 - as in the example
above -
which
is hopefully easier to read.

"Bob Bridges" wrote:

Doesn't such a calculation necessarily include a use rate? That
is, if you
have 64 000 of NET001 now and you never use any, then you'll
have
64 000 of
NET001 for years to come. If you use 2 per day, you'll run out
32
000 days
from now. How can you make a forecast without that datum?

--- "Bam" wrote:
I need to return the date of when my stock will run out based
on
the forecast.

LMCode Avail 1-Sep-08 15-Sep-08 29-Sep-08 13-Oct-08 27-Oct-08
10-Nov-08
24-Nov-08 8-Dec-08 22-Dec-08 5-Jan-09 19-Jan-09 2-Feb-09
16-Feb-09
NET001 64,000 36,000 0 0 48,000 0 0 60,000 0 0 0 48,000 0
48,000

Eg: I have 64,000 of Code "NET001" available. Based on the
forecast, I need
to return the date when my stock will run out. In the above
case
13-Oct-08.

I've used hlookup =HLOOKUP(I2,AC2:AO2,TRUE) but it only
returns
the value
48,000.