Sum Formula with variable range

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?
 
J

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

T. Valko

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top