The formula worked fine for me when I array entered it in cells B1:B20 so
that it was entered as a multicell array formula.

--
Regards,
Tom Ogilvy


"Tajin" wrote in message
...
I can not make this code run that someone here gave me:
I am trying to make a function that makes the average of number of inputs
based on a bigger input qty array, 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 it's absolute.

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