# Moving average of a moving average in the same cell

1. ## Moving average of a moving average in the same cell

Hi.

I have a large spread of data for example cells A1 - A100.

I wish to take a moving average of cells A1-A100 and place this data in cells B1-B100. I then want to take a moving average of the results in cells B1-B100and place them in the same cell arrangement, effectively taking a moving average of a moving average within the same cell arrangement.

Depending upon the frequency spread of my data i may need to do this up to 10 times and place the 10th moving average in the same column as the first moving average replacing the data the original moving average data.

Any help would be much appreciated!

Phil  Register To Reply

2. ## re: Moving average

A1-A100 and place this data in cells B1-B100
Moving average starts after taken the average of n samples, so what is the value of B1 .. Bn-1? ``Please Login or Register  to view this content.``  Register To Reply

3. The value of cell B1 is the same as the value as A1. The first few values do not really matter in this case, it is only when about twenty values in that the data becomes important.

My first few bits of data are as follow:

A1 = 68.066
A2 = 69.040
A3 = 70.215
A4 = 71.777
A5 = 74.316
A6 = 90.332
A7 = 81.738
A8 = 75.293
A9 = 90.918
A10 = 87.201

The data then tends to zero over the next 90 values.  Register To Reply

4. Is this what you are looking for?
B1: =AVERAGE(A\$1:A1) and copy down, and right
After the 10th column, Copy all values in column 10 and Paste special as Values to replace the values in column A
//Ola  Register To Reply

5. ## re: Moving average of a moving average in the same cell

This code will make it semi-automatic ``Please Login or Register  to view this content.``  Register To Reply

6. How do i implement this as i have pretty much no idea about macro's.

Thanks.  Register To Reply

7. 1. Copy the code
2. Press the Alt + F11 key
3. Paste

If you get any problems, try to attach the file (or sample file).
Ola  Register To Reply

8. Ok, i have attached an example of the data i have.

If you plot column A against column B (crank angle vs pressure) you will see that about +3.8 crank angle there is a large oscillation. I wish to smooth this oscillation.

The way i do this is by taking a 5 point moving average either side of the value i am trying to calculate. If you look in cell C921 you will see the formula i have used. From cells C1 - C920, these are the same values as B1 - B920, i have copied and pasted. This is not a problem.

So, the moving average formula is then filled down the rest of the rows to the end of the data. What i have done then is taken a second 5 point moving average of the data in column C and calculated it in D and then to calculate the 3rd moving average, used the data in column D and so on.

What i would like to do is just perform this in one column so that the 2nd moving average is layed over the 1st and the third moving average is layed over the second etc. From the graph i have plotted you can see that after about 5 or 6 consecutive moving averages, the curve becomes smooth.

I hope this post has made sense. If not please let me know.

I would be so grateful if someone could help.

Thanks.  Register To Reply

9. ## re: moving average

Play with this.  Register To Reply