Hi folks,
OK Solved! I inserted my code below if anyone needs to see it, it's pretty messy but does the job. Thanks again,
....
..
....
I'm trying to calculate a moving average, as well as a modified moving average, but I'm getting stuck pretty early on.
For those who don't know, a 3 (or 5, or 10) point MA is the average of the last 3 (or 5 or 10) values of a time series. I have a simple time series with a header in column B, I'd like to have the user input how many points they'd like in cell E1 and have the moving average calculated until the bottom of the series.
Dim movavelength As Integer
Dim lastrow As Integer
Dim startaverage As Integer
Dim sumrange As Integer
'Record last row
lastrow = Application.WorksheetFunction.CountA(Range("A:A"))
'Record length
movavelength = Range("E1").Value
' MsgBox (movavelength)
'Where to start MA
startaverage = movavelength + 1
'Range over which to sum
sumrange = movavelength - 1
'Calculating MA
Dim i As Integer
Dim j As Integer
Dim k As Integer
For j = startaverage To lastrow
For i = 1 To movavelength
Dim tempcalc As Double
tempcalc = Range("B" & j).Value
Range("C" & j).Value = tempcalc
For k = 1 To sumrange
Range("C" & j).Value = Range("C" & j).Value + Range("B" & j).Offset(-k).Value
Next k
Range("C" & j).Value = Range("C" & j).Value / movavelength
tempcalc = 0
Next i
Next j
Bookmarks