UDF to calculate moving average

[volabos]

Hi all, I have written following UDF to calculated moving average, with window size 12:

Option Base 1

Function vv(data As Range) As Variant
Dim n As Integer, i As Integer, j As Integer, number_year As Integer
Dim summ As Double

n = data.Rows.Count

ReDim mov_avg1(1, 1 To (n - 11)) As Double

For i = 1 To (n - 11)
summ = 0
For j = i To (i + 11)
summ = summ + data(j)
Next j
mov_avg1(1, i) = summ / 12
Next i

vv = mov_avg1
End Function

However when I use this UDF I get all value same as mov_avg1(1). Can anyone please clarify me where I made mistake?

Regards,

[Bob Phillips]

Is this meant to be an array formula?

Can you give an example of the data and expected results?

[volabos]

1st take these observation:
2755.87 2967.391 3104.2 3388 3590.385 3982.955 3832 3731.522 3765.217 3665.217 3676.042 4203.125 4306 4384.524 4516.667 5073.958 4851.042 4829 4452.885 4693 4848.958 5154.808 5347.917 5317.391

Now consider another few observation :

3555.16 3684.338 3802.432 3920.138 4060.634 4165.689 4236.193 4287.933 4368.056 4458.368 4582.501 4721.824 4814.679


See here : '3555.16' is the average of 1st 12 observations od 1st colum. similarly '3684.338' is the average of 2nd 12 observarions and so on. Hope this help

[Bob Phillips]

Gotcha



Function vv(data As Range) As Variant
Dim n As Integer, i As Integer, j As Integer, number_year As Integer
Dim summ As Double

n = data.Rows.Count

ReDim mov_avg1(1 To (n - 11)) As Double

For i = 1 To (n - 11)
summ = 0
For j = i To (i + 11)
summ = summ + data(j)
Next j
mov_avg1(i) = summ / 12
Next i

vv = Application.Transpose(mov_avg1)
End Function

[volabos]

Thank you Xld so much.

[mdmackillop]

Hi Volabos
To mark a thread solved, use the Thread Tools drop down
Regards
MD