Excel 2007 : How to create scalable workbook to measure moving average lifetime on an Index

Dear Excel-forum,

I am currently writing a working paper where I am doing some time series analysis on S&P indexes' average stock lifetime, and are having some difficulties creating scalable and effiecient workbooks.
What I need is a workbook that includes the lifetime of all stocks listed before a series of given dates (monthly) in order to calculate moving average lifetime, turnover rate and actual number of delistings per period.

I have attached a list of listings/delistings for S&P100 with their respective dates and durations. SP100.xlsx

Any help would be greatly appreciated. Many thanks in advance!

/vonborge

Dear Søren,

Sorry for the late reply. Your answer is kind of correct, but not quite what I was looking for. However, I managed to extract different data that gave me better measure, so figured out how to do it
By scalable I mean that I have data sets with many more observations, so the procedure cannot be restricitve to just that model.

On the other hand, I am still looking for a way to count the monthly additions (i.e. number of stocks per month from the THRU variable) in the attached spread sheet above, if you have any suggestions for that it would be greatly appreciated! Many thanks in advance

Cheers,
Borge

That formula does not give me number of additions per month per year..? All it produces is a decreasing number of additions over time, and the second formula produces what looks to me like a moving average? Sorry if I am not following here...
What I need is a way to count each addition per month per year, i.e. how many listings where made on the index in November 1989 (should be 1), December 1999 (should be 2) and so forth for all months in the dataset. Any ideas?