# Copy formula with changing references

Need your help because I am not sure of the correct formula to use (if ever there is one!) in Excel 2010.

Say in column A, I have my figures (A1 to A1000). Suppose in column B I need to get the average of A1 to A10. So for B1 my formula is =average(A1:A10). Then for B2, next to get the average of the next 10 figures in column A, i.e., A11 to A20. Is there an easier way to do this? Because if I just copy/drag the formula, B2 becomes =average(A2:A11) when what I need is for it to be =average(A11:A20).

-

You have to take a different approach since just copying and dragging will, as you note, just shift the range for the average by down by 1 row at a time.

This formula uses the OFFSET function to shift the range for each average by 10 rows.

=AVERAGE(OFFSET($A$1,10*(ROW(A1)-1),0,10,1))


OFFSET is used to refer to one or a group of cells that is a given number of rows and columns away from a given cell and a given number of cells wide and tall.

For example, OFFSET(A1,10,0,10,1) refers to a range that is 10 rows down and 0 columns to the right of cell A1 and is 10 rows tall and 1 column wide, which works out to the range of cells A11:A20.

Copying the formula down the column results in the average first for cells A1:10, then cells A11:A20, then A21:30, etc.

-