Forum Discussion
Running mean.
- 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.
