Total Open Positions

[maninjapan]

Im analyzing some performance data from an equities trading strategy and I need to calculate the number of open positions at any one time. Its obviously got to do with overlapping entry and exit dates, but I cant seem to get my head around it. I'd rather do this with a formula but any method that can help me solve this will be much appreciated.
I have attached a sample of the data. Any ideas????

Thanks in advance

[mancubus]

if open pos means "exit date" greater than a specific date then perhaps


=COUNTIF(F:F,"<"&DATE(1995,6,14))-COUNTIF(F:F,"<"&DATE(1995,5,17))


current date ise 5/17/1995 and i want to learn the number of opens as of 6/14/1995

[maninjapan]

I have a feeling thats heading in the right direction but how can I reference the date in column F, rather than the fixed date in that example?

[mancubus]

you can use a cell reference instead of "DATE(1995,6,14))" bit.

[maninjapan]

Thanks Mancubus, COUNTIF with reference to the date got me exactly what I needed!!

[maninjapan]

Related to a similar excel I need to keep keep track of open positions.

Column E is entry signal (Shows "entry" if conditions meet entry conditions)
Column F is exit signal (Shows "exit" if conditions meet entry conditions)

I need column G to return 1 when column E returns "entry" and then continue to return 1 until Column F returns "exit". Can only be a max of 1 position open at any one time.

[mancubus]

the text or formula result in cell in col E is "entry" and the text or formula result in corresponding cell in col F is not "exit".

if this is the case, in G2

=IF(AND(E2="entry",F2<>"exit"),1,0)
copied down to desired row.

instead of 0 at the end, you may use some other text, number, formula, value.