Alterable moving average interval

[sugarandfat]

Hi. I’ve got a long string of data which I want to graph the moving average of, but I want to be able to fine tune the interval in order to get the right resolution of curve, so that it matches some other data as closely as possible.

What would be ideal would be just having the interval as a number in a cell which the equation references, and updates the average whenever this is altered.

Is there a way to do this, or am I going to have to reapply the average every time I want to tweak it?

Cheers.

[EarnestAl]

I am not an expert on this and probably someone will have a quicker, more elegant way but here is a solution.

Alter cell C1 to alter the range of the averages, e.g. 12 cells.

MovingAverageDefinedCell.ods

Cheers
Alistair

For the range 2 cells before to 2 cells after the referenced cell (5 cells) i have put in two cells to alter otherwise you couldn’t have an even number to average. Have a look at the the formula working from the centre outwards. I have a brief explanation on the sheet.

MovingAverageVariableRange.ods

[sugarandfat]

That seems pretty good, thanks for that.
As I understand it it averages the next n cells with the one in question, would there be a way to have it instead centered on the current cell?

ie, if the interval were set as 5, it’d average the current cell with the two previous and the two following, rather than as it currently is with the four following?

Cheers.

[sugarandfat]

Update one seems perfect, much thanks.

[samtyler56]

THIS!!! It’s the question that made me sign up to ask, bc I couldn’t find the answer in normal search for some reason. Big thank you.