Sum Formula with variable range

  • Thread starter Thread starter Migo1
  • Start date Start date
M

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?
 
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.
 
Try this...

Assume you enter the 1st formula in D1.

A1 = 3

=AVERAGE(OFFSET(B$1,ROWS(D$1:D1)*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.
 
Back
Top