Formula for average period calculation

[stepan1987]

Guys, please help with this tricky one:

I have three columns:
A - quantity of security bought or sold in a transaction
B - OPEN/CLOSE: whether the transaction increased the inventory (positive or negative) or decreased it
C - date of the transation

As the security is being bought or sold its inventory changes (gets positive, negative or nil). When there is a negative inventory (security has been more sold than bought ) a buy is a closing position. The same vice versa: a sell is closing to a positive inventory.
What I need: everytime there is a closing transaction I need to calculate the average holding period for the security.

Say there were buys:
100 1 March
300 5 March
200 9 March

and everything was sold on 11 March

I need the formula to give me 100/600*(10 days)+300/600*(6 days)+200/600*(2 days) = 5.33 days

Say, there was a closing transaction and I need this formula to take date of that closing transaction and then weight the difference between the dates of it and respective opening transactions but BEFORE the previous closing transaction occured.

Hope it makes some sence))
Could you give some ideas what the formula should look like?

Thanks!

 

[Supremo626]

Hey, Stepan:
I hope I understand your question, and I'm sure someone else can probably provide a better formula, but in the mean time, try this:

Based on the example you gave:
Column A Column C
1. 100 1-march
2. 300 5-march
3. 200 9-march
4. 600 11-march

Format column D to "Number." In cell D1, enter "=(A1/A$4)*(C$4-C1)" and copy down to D3. In cell D4 (or any other cell you prefer) enter =sum(D1:D3).

In the formula, A$4 is the total sales, C$4 represents the date everything is sold.

I hope that helps!

-Juan