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.
 

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

Similar Threads

Summing rows using formulas with variables 1
Sum of a range that varies 1
Excel formula, sum the value cell and if empty cell left blank 0
Variable Sum Function 6
Formula Content linked to other cells formula Content 5
Variable row number in Sum($A$1:A10) formula 3
Cell reference formula problem 5
Formula Needed : Sum on Conditions 3

Back
Top