When using a rolling average, is this possible

[JW118877]

First of all, thanks in advance if anyone can answer this for me.

This is the scenario.... I am tracking a running average of the last 10 days
worth of statistics. What I would like to do is this.....

Say I have 10 days worth of information already, on the 11th day, I want to
key in the new data and have it drop the last value out of the formula
(equation). What I envisioned was having, for instance, cell A2 open to input
the next days data, and cells A3-A11 to have the last 10 days worth of data.
So when I key in a value to A2 it will automatically shift the column down
and A3 will be the value I just keyed in, while also, kick out the value that
was in column A12. I know this probably seems a little complicated by the way
I am explaining it. I will give a brief visual example below. Thanks again.

Current data
A
1
2 9
3 12
4 8
5 7
6 11
7 3
8 9
9 14
10 3
11 7

New scenario
A
1 new value + enter (ie. 10)
2 9
3 12
4 8
5 7
6 11
7 3
8 9
9 14
10 3
11 7

New result
A
1
2 10
3 9
4 12
5 8
6 7
7 11
8 3
9 9
10 14
11 3

 

[Shane Devenshire]

Hi,

You will need to use VBA code to do this. You could avoid that if you
entered the data at the bottom of the list. You can record the step you use
and then put them in a macro such as this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A2"))
If Not isect Is Nothing Then
'Your code here
End If
End Sub