I've been tearing my hair out over this one so help is much appreciated. I have an excel worksheet with the following data for an index of stocks. (This is an abbreviated version. The table is 60000 lines long, hence the need for a looping code).

Column one contains the stock components of an index.

Column two contains forward looking fiscal years

Column three contains Earnings Estimates by a variety of analysts(this column is hidden as it's not required). So for stock1 there are 3 analysts covering it, each with a different forecast of figures per year. I want to tally the means of these forecast, per year for each stock.

[col1]--[col2]-----[col3]------------[col4]

stock1 Year 1 Earnings figures [average(earnings-yr1-stk1)

stock1 Year 1 Earnings figures

stock1 Year 1 Earnings figures

stock1 Year 2 Earnings figures [average(earnings-yr2-stk1)

stock1 Year 2 Earnings figures

stock1 Year 2 Earnings figures

stock1 Year 2 Earnings figures

stock2 Year 1 Earnings figures [average(earnings-yr1-stk2)

stock2 Year 1 Earnings figures

stock2 Year 1 Earnings figures

stock2 Year 2 Earnings figures

stock2 Year 2 Earnings figures

stock2 Year 2 Earnings figures

stock3 Year 1 Earnings figures

etc.

What I want to do is the following:

A code that loops through the stocks and for each range of earnings figures per year, calculates the average (mean) of this range and dumps the result in the fourth column next to the first line (row) of that range.

If the solution is easy, I can't see it. I've tried by looping through the 2nd column and adding to a dynamic array all the figures until the value in col2 changes. This seems convoluted and doesn't seem to work anyway.

Many thanks

JP