LookUp (Date) Value from Forecast Data
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.
|