Menu
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!
470
Put the following formula in the next column to your data starting in row 7 and then just paste to the bottom of your data... Enter the formula, =AVERAGE(OFFSET($A$1,ROW()*7-1,,-7)), into cell B1 and Copy down to cell B200.
AutoSum lets you find the average in a column or row of numbers where there are no blank cells. Click a cell below the column or to the right of the row of the numbers for which you want to find the average. On the HOME tab, click the arrow next to AutoSum > Average, and then press Enter.
In Excel, have you ever tried to average every 5 rows or columns, that is to say, you need to do these operations: =average (A1:A5), =average(A6:A10), =average(A11:A15),…of course, you can apply the Average function to get the average of every 5 cells every time, but, if there are hundreds and thousands cells in your ...
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.
B1 it would be =AVERAGE(A1:A10)
B2 it would be =AVERAGE(A11:A20)
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,...
104
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
