MrExcel Publishing
Your One Stop for Excel Tips & Solutions

MS EXCEL Moving Avg addin


Posted by Ruth Jeter on October 30, 2001 9:53 AM

I need to set up a monthly moving avg but I do not know how to use the moving avg function under the tools menu... Can I get some insight?


Posted by Mark W. on October 30, 2001 1:31 PM

Suppose A1:A5 contains {1;2;3;4;5} and you want
a moving average of 3 values. Enter A1:A5 as the
"Input Range". Enter 2 as the "Interval". Enter
B1 and the "Output Range". Press [ OK ] and
there you have it!

Posted by Aladin Akyurek on October 30, 2001 1:45 PM

Ruth,

I'm not sure if this is going the kind of reply that you hope for.
Lets say that we have in A1:A9 the following numbers:

{23;34;56;56;78;65;67;45;67}

If we want a moving average with an interval of 3, meaning that the first avg is of < 23, 34, 56 >; the second of < 34, 56, 56 >, etc., we need to carry out the following steps.

Activate the first cell of data.
Activate Tools|Data Analysis.
Select Moving Average in the Data Analysis window.
Input Range will be OK (if not adjust).
Check Labels in first row if you have a label/column heading above the first number.

Select a cell for Output range under Output Options in the same worksheet.
Check Chart output if desired.
Check Standard errors if desired.
Click OK.

The result columns will contain #N/A's for the first 2 corresponding values. But that is OK.

I'm not sure what "montly data" you have. Is it a single per month or ~30 values for each month and that for n number of months.

Aladin

========

Posted by Aladin Akyurek on October 30, 2001 1:51 PM

Didn't see your reply :(