Sum Formula with variable range

[Migo1]

I need to calculate the moving average in column C for a long list of numbers
in Colum B. If the variable is in cell A1 and it = 3, then each sum formula
should sum a range of the rows, such as; Sum($B$1:$B3),
A1 is changed to 5, then Sum($B$1:$B5), where the variable in cell A1 is the
row number of end of the range.
How can this be accomplished?

 

[Jim Thomlinson]

One way...

=SUM(OFFSET($B$1,0,0,A1,1))

You could also use indirect but then you would not be able to drag your
formula if you needed to.

 

[T. Valko]

Try this...

Assume you enter the 1st formula in D1.

A1 = 3

=AVERAGE(OFFSET(B$1,ROWS(D$11)*A$1-A$1,,A$1))

Copy down as needed.

D1 = average of B1:B3
D2 = average of B4:B6
D3 = average of B7:B9
etc
etc

If A1 is an empty cell you'll get an error.