SOLVED

Running mean.

%3CLINGO-SUB%20id%3D%22lingo-sub-2766845%22%20slang%3D%22en-US%22%3ERunning%20mean.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2766845%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20a%20running%20mean%20of%20100%20in%20noisy%20data.%20Can%20I%20fill%20in%20the%20mean%20for%20the%20first%2099%20obs%20with%20part%20means.%20I%20can%20do%20this%20one%20row%20at%20a%20time%20but%20can%20I%20get%20excel%20to%20do%20it%20automatically%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2766845%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2767231%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20mean.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2767231%22%20slang%3D%22en-US%22%3E%3CP%3EYou've%20had%20quite%20a%20few%20views%20and%20no%20replies.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20I%20suggest%20that%20you%20revise%20your%20question%20so%20that%20we%2C%20uh%2C%20can%20figure%20out%20what%20you%20%22mean.%22%20%22Mean%22%20in%20this%20sense%20having%20to%20do%20with%20%3CU%3E%3CSTRONG%3Emeaning%3C%2FSTRONG%3E%3C%2FU%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20not%20really%20clear--although%20it%20no%20doubt%20is%20in%20your%20mind--what%20you%20mean%20by%20your%20description%20of%20%22running%20mean.%22%20So%20a%20fuller%20explanation%20of%20what%20you're%20doing%20and%20what%20you're%20wanting%20to%20achieve%20by%20doing%20that.....would%20help%20you%20get%20some%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2769751%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20mean.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2769751%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Lillioja_0-1632213358224.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311756i1C8426368F92F0C5%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Lillioja_0-1632213358224.png%22%20alt%3D%22Lillioja_0-1632213358224.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ERaw%20data%20blue%2C%20running%20mean%20in%20red%20(moving%26nbsp%3B%3CSPAN%3Eaverage)%20period%20of%2010%20done%20by%20excel.%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2769753%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20mean.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2769753%22%20slang%3D%22en-US%22%3EGets%20smoother%20still%20with%20a%20period%20of%20100%20but%20then%20there%20is%20a%20gap%20between%200%20and%20100.%20I%20can%20fill%20this%20in%20by%20hand%20but%20I%20thought%20there%20might%20be%20a%20short%20cut.%3C%2FLINGO-BODY%3E
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.

@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 (Occasional 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.