excel averaging every 10 rows

Question

I have a big data set which has about 9000 rows. I have a few variables for every year from 1960 onwards, and I need to average them in ten year bins. So I have something like:

1    
2    
3    
4    
2    
3    
4    
5

Now I need to average the first ten rows, then the next ten, and so on, for all 9000-odd rows. I can do that, but then I get all these rows averaged in the middle which I don't need, and I can't go about deleting those many rows. There has to be an easy way to do this, surely?

Would appreciate any help!

Answer 1

Suppose your data starts from A1. Try this one in B1:

=AVERAGE(INDEX(A:A,1+10*(ROW()-ROW($B$1))):INDEX(A:A,10*(ROW()-ROW($B$1)+1)))

and drag it down.

• in B1 it would be =AVERAGE(A1:A10)
• in B2 it would be =AVERAGE(A11:A20)
• in B3 it would be =AVERAGE(A21:A30)

and so on.

General case

If your data starts from An (where n is 2,3,4,...), use this one:

=AVERAGE(INDEX(A:A,n+10*(ROW()-ROW($B$1))):INDEX(A:A,n-1+10*(ROW()-ROW($B$1)+1))

where you should change n to 2,3,4,...