Custom Average Function on Array Failing on NULL Values

[rkaczano]

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.

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

 

[rlv01]

What is it that you are defining as a NULL value?

 

[rkaczano]

I am literally filling a 3d arr within an If stmt saying if a condition is TRUE Then fill arr with the a value tied to a class object property that has been defined as a Double. Otherwise fill arr with NULL. I then pass arr to the Average function and perform an average on a column as defined by the column variable I pass to the average function. The result of the average is then passed to a second 2d array called arr2.

Both arr and arr1 are defined as variants (e.g. Dim arr () as Variant)

This all worked when I was simply filing arr with obj.value. But when I added the conditional logic with the NULL nuance, the average function started failing.

If var1 = True Then
arr(Count1, column, Count1) = obj1.value
Else
arr(Count1, column, Count1) = Null
End If

arr2(Count1, column) = AverageFunction(arr, column, Count1)

 

[rlv01]

Do you need to use Null? If, for example, you used a string value like "n/a" instead, the average function would ignore it and average the numbers.