I am new to VB and I have run into some trouble. I am trying to create a function that will calculate the average of a range, but omit all zeroes. The formula for this operation is not an issue, it is defining the argument of the function as a range of values from the spreadsheet. I would like the function to work just like any of the other Excel functions, where you can type "=zavg(" and then click the cell range you want. Here is the code I have so far...
Function zavg(num As Single)
If application.Sum(num) = 0 Then
zavg = 0
Else
zavg = (application.Sum(num)) / (application.SumProduct((num <> 0) * 1))
End If
End Function
Function zavg(num As Single)
If application.Sum(num) = 0 Then
zavg = 0
Else
zavg = (application.Sum(num)) / (application.SumProduct((num <> 0) * 1))
End If
End Function