Stock Return Calculation

[proxax]

Dependent Variable: Date
Changing Variables: Values in two different columns

I'm calculating return for a stock (-5, 5), (-2, 2) and (-1, 1) days from the date of announcement of a deal and doing the same for a market index.

As you would have figured by now, the date of announcement changes for every deal. I need to calculate the average value of stock prices 5 cells above/before the selected cell (which is the day of announcement) and 5 days below/after.

My sample size has gone upto 141 deals and isnt 10 anymore. I am now looking for an easier way to calculate averages of these days before and after the date of announcement.

Thank you for reading!
Proxax

 

[proxax]

SIMPLIFYING IN MATHEMATICAL TERMS

Column A: Date (DDMMMYY)
Column B: Nasdaq Composite (peer group avg)
Column C: Company 1 Share Price
Column D: Company 2 Share Price
Column E-ZZ: Company 3-52 Share Price(s)

A B C

1/1/01 1001 25
2/1/01 1002 26
3/1/01 1003 27
4/1/01 1004 28
5/1/01 1005 29
6/1/01 1006 30 > Date of Announcement
7/1/01 1007 31
8/1/01 1008 32
9/1/01 1009 33
10/1/01 1010 34
11/1/01 1011 35

Return for Company1 = [(Average C1:C5)-(Average C6:C10)]/C5, in percent

Return for Nasdaq Composite or Peer Group = [(Average B1:B5)-(Average B6:B10)]/B5, in percent

Now for every Company from 1 to 141 in my list the date of announcement changes and the reference for calculation goes awry, the dragging of formulae cells does not help at all and I have 141 different deals (one per company) to calculate.

]I might not know how to do this but I am sure this isn't much to ask in the 21'st century... or is it?

VERY PERPLEXED AND KNOW IT IS BETTER TO ASK WHEN IN DOUBT!
PLEASE HELP

 

[guyintrouble]

I have to calculate cumulative abnormal returns as wel but have another problem.

I have the daily stock data of 500 firms from 1996 till 2012 and the announcement data of their acquisition.
To calculate the CAR, I need a kind of search function which displays the 'clean period' between 110 and 10 days prior to the announcement date for the 500 firms.

Concrete, next to the kolumn of the stock data from 1996 till 2012 of a firm, a need the data from 110 to 10 days prior to the announcement date