Using the Autofill feature skipping calculated cells.

[zero]

Suppose I have data values from A1 to A6 and it consists of 2 sets of data, one of which is from A1 to A3 and the other is from A4 to A6.

I want to simply the average of each set by inserting one formula and using the drag down feature.

B1 = AVERAGE(A1:A3), however when I drag this formula down it becomes B2 = AVERAGE(A2:A4). How can I make it B2 = AVERAGE(A4:A6) without manually inputting it?

I ask this because I have my data in rows of 3 and a lot of it! I don't want to manually input the reference cells everytime. Also I already tried to highlight 3 manually input of the formula and dragging it down, hoping that it would recognize the pattern but it does not.

I'm using Excel 2010.

 

[zero]

Can anyone help me?

 

[John Davis]

Can anyone help me?

There are probably better ways, but I think you could use a helper column to accomplish this.

Say for instance your helper column was "E"

Then in "E1" you put a zero, in E2 you put =E1+2 and then drag down Column E

Then in B1 enter the below formula and drag down.

=AVERAGE(OFFSET(B1,E1,-1),OFFSET(B1,E1+1,-1),OFFSET(B1,E1+2,-1))