how do you skip empty cells or zeros when calculating the average

[soccerdav2003]

In my calculation where I find the last row and calculate the average of the
last 30 sets of data, I come across zeros. How do I ignore zeros in VB
macros.

Ex.
0 (or empty set in some cases)
3
6
9
12
average = 6 in Macro
The average should be 7.5 because first value was zero (or nearly zero)
which means I'm going to have to do some kind of test that says if the zero
value is less than .01, ignore it when calculating the average.

 

[Billy Liddel]

Something like this should do it

Function myAvg(ByVal data) As Double
Dim count As Integer, mySum As Double
For Each c In data
If IsNumeric(c) And c >= 1 Then
mySum = mySum + c
count = count + 1
End If
Next
myAvg = mySum / count
End Function

Peter