# 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.

-