Sep 20 2021 05:14 AM
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?
Sep 20 2021 06:46 AM - edited Sep 20 2021 06:47 AM
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.
Sep 21 2021 01:46 AM
Sep 21 2021 01:48 AM
SolutionSep 21 2021 07:19 AM
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.
Sep 22 2021 07:17 AM
Sep 22 2021 07:35 AM - edited Sep 22 2021 07:37 AM
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.
Sep 21 2021 01:48 AM
Solution