Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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