Moving average on dynamic named range without helper column

Hello all,

I would like to make a dynamic moving average on the column of data that has no fixed length. I would like the moving average to be plotted on the chart so the change of moving average parameter is visible instantly.

I have to stick to .xlsx extension, so I cannot use VBA or macros.
Length of the data column is changing so in case of using a helper column it should (probably) be expanded to the whole length of the column (to incorporate the "worst-case" scenario). That significantly adds to the file size and slows down the process even for a very short raw data column. So, no helper column.

I managed to get to the following formula (rawdata is dynamic named range for the data column, $C$4 holds the parameter)

=AVERAGE(OFFSET(rawdata;ROW(rawdata)-1;0;$C$4;1))

It seems to work when entered as array formula. When I try to define it as a new named range to put in on the chart it fails (see the attached workbook).
I use Excel 2010.

Any idea is greatly appreciated.

Hi
Try this regular formula from D3

Formula:

=SUMPRODUCT(A$1:A$100*(ROW(A$1:A$100)<=ROW())*(ROW(A$1:A$100)>=ROW()+$C$4))/-$C$4

Hi José,

thanks for the response. Your solution works (the second equal sign is too much, though), but it produces a scalar, not a range. If I define new named range with your formula and try to chart it I get just one point. Do you know how to adapt your formula to produce the whole range?
Thanks!

Hi
I have a array formula that produce a array with the correct values (see in file the range O1:O17)
and I set the range name MovAverage but could not pass it to the chart. See if this interests you.

Formula:

=AVERAGE(INDEX(rawdata,IF(ROW(rawdata)+$C$4+1<1,1,ROW(rawdata)+$C$4+1)):INDEX(rawdata,-$C$4+IFERROR(ROW(rawdata)+$C$4,0)))