LookUp (Date) Value from Forecast Data

[Bam]

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?

 

[Bob Bridges]

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]

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.

 

[Stefi]

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:

 

[Stefi]

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


„Stefi†ezt írta:

 

[Bam]

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.

 

[T. Valko]

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)

 

[Bam]

Thankyou Biff.

That seems to work!

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)

 

[T. Valko]

You're welcome. Thanks for the feedback!