Calculating average inventory period

[stepan1987]

Hi guys,
Could you please advise how can I calculate the average holding period (with networkdays). I have the following columns

Inventory
In/outflow (with respective sign)
Date

I need to calculate the avg holding period for every available date.
Say there was 500 input on 1/10/2011 and 300 input on 1/21/2011 and another inflow (say, 100) happened on 1/28/2011.

I need the formula to give me for 1/31/2011
(networkdays(1/31/2011;1/28/2011;holidays) * 100 + networkdays(1/31/2011;1/21/2011;holidays) * 300 + networkdays(1/31/2011;1/10/2011;holidays) * 500) / (100 + 300 + 500)

This formula should be calculated everytime there was an outflow and should incorporate all dates and inflows before the previous outflow occurred.

I d be happy to clarify everything that seems unclear..
Could you please help me out?

 

[stepan1987]

The hardest thing I cannot crack here is how to incorporate something like a BEFORE function.
SUMPRODUCT ((inflow date - latest outflow date) * inflow befote latest outflow) / SUM (inflow before latest outflow)

 

[stepan1987]

Do you have any ideas guys??

 

[stepan1987]

OK, is there at least some formula that can mimic this "sum before value in the column shows up" function?