Give this function a try. It must be "array entered" in the worksheet. For
example, select cells B1:B20 and then type the formula: =myavg(A1:A20,4)
Finish the entry with: Ctrl Shift Enter

The function only works for a single column of source data and the formulas
must be entered down a column and not across a row to get your desired
result.

Troy


Function myavg(DataRange As Range, MaxN As Long) As Variant
Dim lngR As Long
Dim lngI As Long
Dim dblNext As Double
Dim dblSum As Double
Dim vArr() As Variant

'Verify that the DataRange is a single column.
If DataRange.Columns.Count 1 Then
'Return the "#N/A" error to the cells.
myavg = CVErr(xlErrNA)
Exit Function
End If

lngR = DataRange.Rows.Count
For lngI = 1 To lngR
ReDim Preserve vArr(1 To lngI)
dblNext = DataRange(lngI, 1).Value
dblSum = dblSum + dblNext
If lngI <= MaxN Then
vArr(lngI) = dblSum / lngI
Else
dblSum = dblSum - DataRange(lngI - MaxN, 1).Value
vArr(lngI) = dblSum / MaxN
End If
Next lngI

myavg = Application.Transpose(vArr)
End Function


"Tajin" wrote in message
...
You are right, thanks, but still does not work. This is what I am trying
to
achieve.
Let's say we have on A1 to A20 some data I want to average, and I want to
average on 4, my function will be inserted on B1 to B20, cell B1 has same
value as A1, B2 has (A1+A2)/2, B3 has (A1+A2+A3/3, B4 has (
A1+A2+A3+A4)/4,
B5 has (A2+A3+A4+A5)/4, .... B20 has (A17+A18+A19+A20)/4, I made some
correction to the code to adapt to qty of 4, this number is the same for
the
whole array output

Function my_avg(data(), j As Integer) As Double()

ReDim my_avg(UBound(data))

For i = 0 To UBound(data)
If i < j - 1 Then
my_avg(i) = (my_avg + data(i)) / (i + 1)
If i = j - 1 Then
my_avg(i) = (my_avg + data(i)) / j
If i j - 1 Then
my_avg(i) = (my_avg + data(i) - data(i - j)) / j
End If
Next i
End Function