Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LookUp (Date) Value from Forecast Data
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LookUp (Date) Value from Forecast Data
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LookUp (Date) Value from Forecast Data
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LookUp (Date) Value from Forecast Data
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LookUp (Date) Value from Forecast Data
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LookUp (Date) Value from Forecast Data
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Forecast a date from data given | Excel Discussion (Misc queries) | |||
lookup data betwaeen two date | Excel Discussion (Misc queries) | |||
how do I fix a forecast date that has passed for a zero value? | Excel Discussion (Misc queries) | |||
Forecast function and Date option | Excel Worksheet Functions | |||
How can I Lookup and sum payments for cash flow forecast? | Excel Worksheet Functions |