[Solved] Moving average

[robbyn]

I have a special function that can give me the historical closing price of a share.

Assume i have used the function to create a column of 200 closing previous share prices for each work day for a share. .

What is the easiest way to create a list of 100 moving average share prices?
It will involve this:
A. Add the first 200 share prices, average, put result into a new column.
B. Next day do same for 200 share prices starting from 2nd share price in list
C Same thereafter going down one day at a time. so only 200 share prices are averaged for any one day.

this action has to be done for 10 different shares

[robleyd]

Let's assume that your prices are in column A, say from A2:A2000

In B201 put the formula

Code: Select all

=AVERAGE(A2:A201)

and drag down as far as needed. Use the black square at bottom right of the active cell to drag it. The formula will adapt as it is dragged down to include only the previous 200 cells.

This leaves the first 199 cells. In A2 enter the formula

Code: Select all

=AVERAGE($A$2:A2)

and drag down as necessary. Of course, don't bother with this if you don't need the average for less than 200 prices.

See [Tutorial] Absolute, relative and mixed references if you don't understand what the dollar signs in the formula do.

[robbyn]

Thank you. I can take it from there.