Running mean.

Occasional Contributor

I am using a running mean of 100 in noisy data. Can I fill in the mean for the first 99 obs with part means. I can do this one row at a time but can I get excel to do it automatically?

6 Replies

You've had quite a few views and no replies.


Could I suggest that you revise your question so that we, uh, can figure out what you "mean." "Mean" in this sense having to do with meaning.


It's not really clear--although it no doubt is in your mind--what you mean by your description of "running mean." So a fuller explanation of what you're doing and what you're wanting to achieve by doing that.....would help you get some help.



Raw data blue, running mean in red (moving average) period of 10 done by excel.








best response confirmed by Lillioja (Occasional Contributor)
Gets smoother still with a period of 100 but then there is a gap between 0 and 100. I can fill this in by hand but I thought there might be a short cut.

If the data is in A3:A1000, you can "work up" to a 100-unit moving average by entering the following formula in B3 and copying down the column:




I use ROW(A$3) instead of simply 3 so that the number is adjust automagically if we move the data later.

I need to learn some more commands to use in excel, but I see where you are going, thanks.

That allows us to enter the same formula into the entire range. But arguably, that might be overkill. A simpler approach:

B3: =AVERAGE(A$3:A3)
B102: =AVERAGE(A3:A102)

Copy B3 into B4:B101 (that is, the first 99 cells). A3 (without "$") changes to A4, A5, etc as we copy down the column. A$3 remains unchanged.  Thus, the range is effectively A3:A3, A3:A4, A3:A5, etc as we copy down the column.

Copy B102 into B103:B1000 (that is, the rest of the column). A3:A102 changes to A4:A103, A5:A104, etc as we copy down the column.