How to use an increment an average formula by more than one row in excel

Question

Say I want to enter the formula =AVERAGE(B3:B5) in the cell below (B6:B8).
How do I accomplish this without manually entering the cell reference? If I copy and paste, I get =AVERAGE rather than =AVERAGE(B6:B8) (B4:B6).

I must be able to increase three rows at once. I tried doing a little searching, but a lot of the results are a little jumbled up and don't really make much sense.

Answer 1

I believe OFFSET makes it simpler, for example, assuming you want the first average in cell D2. Copy down this formula in D2.

=AVERAGE(OFFSET(B$3:B$5,3*(ROWS(D$2:D2)-1),0))

Generally speaking, the B$3:B$5 range indicates the first range you want to average, 3 is the number of rows to increment each time, and D2 is the start cell. Any or all of those can be altered, though, based on your specific requirements.