SOLVED

Running mean.

New 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.

@mathetes 

Lillioja_0-1632213358224.png

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

 
 

 

 

 

 

 

 

best response confirmed by Lillioja (New Contributor)
Solution
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:

 

=AVERAGE(INDEX(A:A, MAX(ROW(A$3),ROW()-99)):A3)

 

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.