Excel – Average for every n row in Excel

microsoft excel

I have a column with about 1000 rows and I want to find the average for every 16 rows and add it in the next column without empty cells between them. I found this formula, which works perfect if my data starts from the first row.

=AVERAGE(OFFSET(INDIRECT("A"&ROW(A1)*16-15),0,0,16,1))

I am trying to make it work for data that starts from the second row, since the first row has the headers. I tried a lot of things without success. Any help?

Best Answer

Consider:

=AVERAGE(OFFSET(INDIRECT("A"&ROW(A1)*16-14),0,0,16,1))

should be placed in B1

If copied down:

B1 will show the average of A2 thru A17
B2 will show the average of A18 thru A33

etc.

EDIT#2:

To get B2 to represent the average of A2 thru A17, use:

=AVERAGE(OFFSET(INDIRECT("A"&ROW(A2)*16-30),0,0,16,1))
Related Question