calculate average of every 5 cells in Excel

I have some data sampled at a rate of 1 sample per minutes. However, to use them as input for the following calculation, I have to convert them to the 5 minutes average. Simply put, I want to let B1=average(A1:A5), B2=average(A6:A10), B3=average(A11:A15), ... til B10=average(A46:A50).

I tried VBA code in Excel but didn't make it work. Then I tried Matlab-the code works but it's troublesome to copy/paste all the data between Excel and Matlab back and forth. Finally I figured out a simple way to do this in Excel, by using the OFFSET, ROW, and INDIRECT functions.

The formula to put in cell B1 is:

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


The Row(A1) returns the row number of the cell, which is '1' in this case. So the INDIRECT("A"&ROW(A1)) *5-4, 0,0,5,1) returns the cell A1 to A5. When dragged to cell B2, the INDIRECT("A"&ROW(A2)) *5-4, 0,0,5,1) returns the cell A6 to A10. So I can drag this formula all the way down to B10 to finish this task.

The file looks like this: