Sum Formula with variable range
 

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?

Sum Formula with variable range
 

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

Jim Thomlinson


"Migo1" wrote:

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?

Sum Formula with variable range
 

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.

--
Biff
Microsoft Excel MVP


"Migo1" wrote in message
...
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?