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.