Formula Moving Average

I have a column which averages another column, to form a moving average series for a graph. For a moving average using a sample length of 4, it looks something like this:

B1 = A1
B2 = AVERAGE(A1:A2)
B3 = AVERAGE(A1:A3)
B4 = AVERAGE(A1:A4)
B5 = AVERAGE(A2:A5)
B6 = AVERAGE(A3:A6)
...
...

Simple enough. Now let's say I have a named range that contains the number of samples (we'll call it "SampleSize"). Right now I'm writing those hardcoded formulas in a VBA loop, and adjusting the first cell in the average based on the sample size. In the above example, I locked the first cell to A1 until I got to the 5th row, at which point it becomes a true moving average. This works fine, but the problem is I have to re-run the loop to create new hard-coded formulas in each cell any time the sample size changes...and that isn't very efficient due to other stuff that is going on in the loop.

How could I write a permanent dynamic formula for each cell, that would account for the variable sample size? Pseudo code would be something like:


B1 = A1
...
...
B4 = if SampleSize >= thisRow then AVERAGE(A1:AthisRow) else AVERAGE(AthisRow -SampleSize + 1:AthisRow)
B5 = if SampleSize >= thisRow then AVERAGE(A1:AthisRow) else AVERAGE(AthisRow-SampleSize + 1:AthisRow)
B6 = if SampleSize >= thisRow then AVERAGE(A1:AthisRow) else AVERAGE(AthisRow-SampleSize + 1:AthisRow)
...
...


The idea seems simple, but I'm drawing a blank. Any help appreciated!

- craig

(colored text seemed to work better than code tags )

I don't understand your example.

B1 = A1
B2 = AVERAGE(A1:A2)
B3 = AVERAGE(A1:A3)
B4 = AVERAGE(A1:A4)
B4 = AVERAGE(A2:A5)
B4 = AVERAGE(A3:A6)
...
...

What is the sequence meant to look like?

Maybe you could post a sample workbook with a mock up of what you want to achieve.

Regards, TMS

Example:

Cells A1:A10 = numbers 1 through 10

Cell B1 = "=A1"
Cell B2 = "=AVERAGE(A1:A2)"
Cell B3 = "=AVERAGE(A1:A3)"
Cell B4 = "=AVERAGE(A1:A4)"
Cell B5 = "=AVERAGE(A2:A5)"
Cell B6 = "=AVERAGE(A3:A6)"
Cell B7 = "=AVERAGE(A4:A7)"
Cell B8 = "=AVERAGE(A5:A8)"
Cell B9 = "=AVERAGE(A6:A9)"
Cell B10 = "=AVERAGE(A7:A10)"

The results of this look like:

Please Login or Register  to view this content.

I think ...

=IF(ROW()<$C$1+1,AVERAGE($A$1:INDEX(A:A,ROW())),AVERAGE(INDEX(A:A,ROW()-3):INDEX(A:A,ROW())))

C4 holds the sample size

Regards, TMS

Close, but...

1. "AVERAGE($A$1:" should be "AVERAGE(A1:"

2. I believe "ROW()-3" should be "ROW()-SampleSize-1"

3. It looks like the index function is returning a value instead of an address (which is normal right?). So the average functions end up looking like this: "AVERAGE(A2:74)". It is averaging two numbers, rather than a range. How can I replace the index function to get an address instead of a value?

I think.

EDIT:

How come this works:

Please Login or Register  to view this content.

But this doesn't:

Please Login or Register  to view this content.

EXCEL says this is an invalid formula, even though the ADDRESS functions return valid address strings. I should be able to separate the two ADDRESS functions with the semi-colon...right?

Hello craig,

I am not sure, I am following you correctly.

Try this in B1, then drag down.

=AVERAGE(INDEX(A:A,MAX(ROW()-3,1)):A1)

I got it...it was just a matter of moving things around and playing with the values. (I named the column "Z" to make the formula easier to read below, and my data starts on row 2, since there is a header).

Please Login or Register  to view this content.

Works beautifully. Thanks for the help and pointing me in the right direction!

Actually, it should be:

=IF(ROW()<=$C$1,AVERAGE($A$1:INDEX(A:A,ROW())),AVERAGE(INDEX(A:A,ROW()-($C$1-1)):INDEX(A:A,ROW())))

But Haseeb's is neater.

You'd need to use Indirect, to answer the other question:

=AVERAGE(INDIRECT(ADDRESS(ROW(),COLUMN(A1),4)):INDIRECT(ADDRESS(ROW() + 3,COLUMN(A1),4)))

but it doesn't give you what you want.

Regards, TMS

Using your named range, and one of my own for column A:

=IF(ROW()<=MovAvgSamples,AVERAGE(INDEX(Samples,1):INDEX(Samples,ROW())),AVERAGE(INDEX(Samples,ROW()-(MovAvgSamples-1)):INDEX(Samples,ROW())))

in B1 and drag down.

Regards, TMS

Sorry: meant to say - use Evaluate Formula to see how this all pans out.