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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.