Auto Populate Horiziontally, with a twist

[scott11]

Hello,

Here is my problem:

I am averaging the numbers in rows 1, 2, 3 in column A in cell A5, so my formula in cell A5 is this =Average(A1:A3). Rows 1, 2, 3 continue on until column AD, with each cell below A5 (A6, A7, etc.) containing the average of the next column in line, example:

A6 =Average(B1:B3)
A7 =Average(C1:C3)
A8 =Average(D1:D3)

and so on. However, when I attempt to auto populate the remaining cells (A9, A10, etc.) excel changes the row numbers. What I need to know is if it is possible to auto populate without changing the row numbers.

 

[Momentman]

By auto-populate, do you mean drag the formula in A8 down to A9, A10.......

 

[scott11]

Yes.

 

[Eric W]

Try this in A5:

=AVERAGE(OFFSET($B$1:$B$3,0,ROWS($A$5:$A5)-1))

and drag down.

 

[scott11]

Nice! That got it, thank you very much!

 

[Eric W]

You're welcome!