Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
When using a rolling average, is this possible
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
When using a rolling average, is this possible
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 -- If this helps, please click the Yes button Cheers, Shane Devenshire "JW118877" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rolling Average | Excel Worksheet Functions | |||
rolling average | Excel Worksheet Functions | |||
Rolling Average | Excel Worksheet Functions | |||
Rolling 3 mth Average | Excel Worksheet Functions | |||
Rolling Average | Excel Discussion (Misc queries) |