Inventory degradation calculation

Thanks for the hint, I need a little more help ( I wasn't clear enough before)

I need to do this for multiple line items, eg

Part Iventory wk45 wk46 wk47 ...... wk53 Week of first procure
XX 100 10 15 20 10 week48
yy 30 5 17 18 10 week 47

I had a simliar problem in the past, and think the answer is somewhere like :

=INDEX(I$2:V$2,MATCH(TRUE,I3:V3>0,0))

(but this would only work out the cell with the first value in it)

Craig,

I tried every way I could think of to get this into a formula. After three failed alternate attacks, I gave up and just wrote a UDF to do it. Here's the spreadsheet:

Formula in C3 and copied down:
="Week "&OFFSET($C$2,0,monthssupply(B3,D3:M3))<hr>

The code for the UDF is:<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> MonthsSupply(rngStartInvent<SPAN style="color:#00007F">As</SPAN> Range, rngDemand<SPAN style="color:#00007F">As</SPAN> Range)<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Integer</SPAN><SPAN style="color:#007F00">' Returns period in which accumulated demand will exceed starting inventory</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> dblInvent<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Double</SPAN>
    
    dblInvent = rngStartInvent.Value
    i = 0
    <SPAN style="color:#00007F">Do</SPAN><SPAN style="color:#00007F">Until</SPAN> dblInvent< 0
        i = i + 1
        dblInvent = dblInvent - rngDemand(i)
    <SPAN style="color:#00007F">Loop</SPAN>
    MonthsSupply = i<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN></FONT><hr>
To add a User Defined Function (UDF) to a workbook:

1. Alt+F11 to get to the Visual Basic Editor (VBE).
2. If WB does not contain any Standard Modules then from the VBE menu Insert|Module
3. Add in code (copy and paste).
4. Alt+F11 to jump back to Excel.
5. In the cell enter the formula using the newly created UDF. Example<ul type=square>
6. =myNewUDF()

[/list]

HTH

Thanks Greg - works great !


Craig

Craig,

As I said, I was just sure there had to be some way of doing this without relying on a UDF, I just couldn't suss it out. So I sent a plea for guidance to our #1 array formula guru. Turns out I was in one of those "so close and yet so far" situations. My logic was good, but I was trying to get SUM to do something it won't; whereas SUBTOTAL(9,) will play nice. So, courtesy of Mr. Aladin Akyurek we have another alternative that doesn't require VBA. Here it is.

Formula in C3 and copied down:
{="Week " & INDEX($D$2:$M$2,MATCH(TRUE,SUBTOTAL(9,INDIRECT(ADDRESS(ROW($D3),COLUMN($D3))&":"&ADDRESS(ROW($D3),COLUMN(D3:M3))))>=B3,0))}
Note that it's an array formula, so confirm with Ctrl+Shift+Enter.

Thanks, Aladin.

FWIW, here is the formula I'd cooked up yesterday, which fails if use SUM, but works if use SUBTOTAL. Again, it's an array formula -

{="Week " & INDEX($D$2:$M$2,MATCH(TRUE,(SUBTOTAL(9,OFFSET(D3,0,0,1,COLUMN(INDIRECT("1:10"))))>$B3),0))}