calculating averages which reset if conditions are met

[jrtaylor]

I've attached a workbook which has 6 columns of data (stock purchased and sold).

The purchases and sales are not "matched" one to one. There are partial sales, and dates where stock is both bought and sold.

Over a period of two weeks, the net shares held may go from zero to 200, and back to zero. Then the cycle begins again.

For each cycle, I want to get the average price of shares, updated daily as shares are bought and sold within that cycle.

I've tried some nested "if/then" formulas, and have almost solved the problem, but it eventually falls apart. (See worksheet, highlighted row).

I'm hoping one of you geniuses can help me find a solution. As always, I'll very much appreciate any help!

[Pauleyb]

So, your new average price should just be the new cost plus the cost of the currently held shares (# shares * avg price) divided by the new total of shares. This should get you the proper weighted average.
In cell R7, then drag down.
=IF(O7+P7,(P6*R6+N7)/(O7+P7),0)

Note, however, that there is a problem when you combine a purchase and a sell at the same time. Not sure why you would do that, but timing is everything. Buying then selling would yield a different average than selling then buying. For this exercise I followed your lead of buying then selling so that our numbers matched until you ran into difficulty.

[jrtaylor]

Hi Pauley, thanks for that help. I appreciate it. Note that this particular strategy involves (but is not limited to) closing out positions at a fixed duration, whether or not a position is immediately reopened. For purposes of this exercise, I am making the assumption that price does not change much over a 10 minute +/- period during the closing/opening of positions.

And I'll take into account your note about short vs long positions. Thanks again!