Macro for moving average and charting

[sramalin]

I have the following in my file,

Sheet 1 is a typical output consisting of five columns (A,B,C,D,E) that I get from my experiment. For my data analysis then, I do a two point moving average ( in Sheet 2) and three point moving average (in Sheet 3) of all the columns in Sheet 1 and plot a graph that consists of two series (i.e. Col B (X) vs Col C (Y) and Col D (X) vs Col E (Y) ). The formula for two and three point average that i use are

Two point moving average: =AVERAGE(INDIRECT("Sheet1!A"&(ROW(A4)-1)*2+1&":A"&(ROW(A4)-1)*2+2))

Three point moving average: =AVERAGE(INDIRECT("Sheet1!A"&(ROW(A4)-1)*3+1&":A"&(ROW(A4)-1)*3+3))


I have hundreds of files like this and inorder to save time and automate the processing I tried recording a macro to do the task for me. But then I encountered many errors and coming from a non-programming background I had no clue what was going on.

Can someone please help me with this. Can a macro be written that can perform this? Any help is greatly appreciated.

Much thanks,

Karthik

 

[sramalin]

And just to clarify, this moving average also reduces the amount of data I have. That is a two point moving average of col A would be

B1 = (A1+A2)/2
B2 = (A3+A4)/2 and so on so forth.

Similarly a three point moving average would be B1 = (A1+A2+A3)/3, B2= (A4+A5+A6)/3 etc.

Thanks.