Good morning,
Many years ago, I asked the questions regarding compute the average with Null value. During that time, I had a total of up to seven fields, between 4-7. The codes that I used were the following:
Public Function basAvgSeven(val1 As Variant, _
val2 As Variant, _
val3 As Variant, _
Optional val4 As Variant, _
Optional val5 As Variant, _
Optional val6 As Variant, _
Optional val7 As Variant) As Variant
Dim MyVal(7, 2) As Single
MyVal(1, 1) = Nz(val1)
MyVal(2, 1) = Nz(val2)
MyVal(3, 1) = Nz(val3)
If (Not IsMissing(val4)) Then
MyVal(4, 1) = Nz(val4)
End If
If (Not IsMissing(val5)) Then
MyVal(5, 1) = Nz(val5)
End If
If (Not IsMissing(val6)) Then
MyVal(6, 1) = Nz(val6)
End If
If (Not IsMissing(val7)) Then
MyVal(7, 1) = Nz(val7)
End If
MyVal(1, 2) = Not IsNull(val1)
MyVal(2, 2) = Not IsNull(val2)
MyVal(3, 2) = Not IsNull(val3)
If (Not IsMissing(MyVal4)) Then
MyVal(4, 2) = Not IsNull(val4)
End If
If (Not IsMissing(val5)) Then
MyVal(5, 2) = Not IsNull(val5)
End If
If (Not IsMissing(val6)) Then
MyVal(6, 2) = Not IsNull(val6)
End If
If (Not IsMissing(val7)) Then
MyVal(7, 2) = Not IsNull(val7)
End If
MyVal(0, 1) = MyVal(1, 1) + MyVal(2, 1) + MyVal(3, 1) + MyVal(4, 1) + MyVal(5, 1) + MyVal(6, 1) + MyVal(7, 1)
MyVal(0, 2) = Abs(MyVal(1, 2) + MyVal(2, 2) + MyVal(3, 2) + MyVal(4, 2) + MyVal(5, 2) + MyVal(6, 2) + MyVal(7, 2))
If (MyVal(0, 2) > 0) Then
basAvgSeven = MyVal(0, 1) / MyVal(0, 2)
End If
End Function
************************************************************
Everything was working perfectly.
Now, a new table has been constructed to do a similar task. However, the number of fields have been increased to 45.
My questions are:
1. Is there any "easy" way for me to do, aside from declaring the val1 until I reach Val45?
2. If there are some ways, can anyone post the codes?
Thank you.
