I have an Average function that is averaging an array based on a column. The array has a mix of values and NULL values. I know it is failing as I can trap the error.,
My goal is to only average the non-NULL values in that array.
Can anyone provide me with some insight into how to treat the NULL values.
My goal is to only average the non-NULL values in that array.
Can anyone provide me with some insight into how to treat the NULL values.
VBA Code:
Function AverageFunction(DataArray As Variant, column As Integer, year As Integer)
Dim X As Integer
Dim rowcount As Integer
Dim tempArray() As Variant
rowcount = UBound(DataArray, 1)
ReDim Preserve tempArray(1 To rowcount)
'Loop through rows of desired column in given year
For X = 1 To rowcount
tempArray(X) = DataArray(X, column, year)
Debug.Print DataArray(X, column, year)
Next
On Error GoTo check:
AverageFunction = Application.Average(tempArray, 1)
check: Debug.Print "Error"
End Function