Average of n elements

[Lattaio]

Hi everyone,
I have a problem which I believe must be very easy to solve... but not for me...

Let's assume I have a time series of several hundred values in column A and I want to calculate a moving average of the last N elements (say 10 elements). if I write "=average(A1,A10)" and then drag down the formula the problem is solved... but then I want to change the value of elements I'm averaging from 10 to 11 and then other several values...

Is there an easy way to do it?
I would like to have an average formula with two parameters: the last value to consider and the number of values to include. In this way I would only have to change the number of values to include and everithing would be done in one click...
Hope anyone can help. Thanks a lot
Lattaio (milkman)

[NBVC]

Something like this:

=AVERAGE(INDEX(A:A,15-C1+1),INDEX(A:A,15))

where C1 contains number of values to average and INDEX(A:A,15) represents your last cell as being A15.

You can also put the 15 in another cell, like C2, if you want to be able to easily change that variable also

Edit: I should have added 1 to C1 in order for the formula to capture the last C1 items only.

[shg]

Or, if the last cell is indefinite, and B1 contains the number of cells to average,

=AVERAGE(INDEX(A:A, MATCH(9E+307, A:A )):INDEX(A:A, MATCH(9E+307, A:A ) - B1 + 1))

[Lattaio]

Hi guys,
thank you very much for your reply. It would be perfect for my problem.
The only thing is:
if I have in cells A1 to A6 the following values: 6,5,4,5,6,7 and use the formula suggested by NBVC (changing 15 with 6) and puttin the value 6 in cell C1 (so I want the average of all the 6 numbers), the result of the formula is 6.5.
Now, the average of those elements is actually 5.5... so, what is wrong in the formula?

In addition, if i put the value 7 in cell C1 instead of 6, i get a different result (but I only have 6 values... so where does the formula takes the 7th value to average?)

Thanks again guys...

[NBVC]

The comma in my formula should have been a colon....

=AVERAGE(INDEX(A:A,6-C1+1):INDEX(A:A,6))

[Lattaio]

Oh , that was easy....

THANK YOU VERY MUCH. Wonderful!!!