I created this VBA code to average one or more ranges of nonzero numbers.
Some example uses would be:
=avgNonZeros(B2:B10,C2:C10,E2:E10)
=avgNonZeros(Q22,R22,U22:Z22)
=avgNonZeros(B2:B10,C10,E10)
=avgNonZeros(B2:B10)
Option Explicit
Function avgNonZeros(ParamArray rangeList() As Variant) As Variant
** 'Returns the average for all nonzeros of rangeList.
** 'rangeList may be one or multiple ranges.
** Dim cell As Range
** Dim i As Long
** Dim totSum As Long
** Dim cnt As Long
** DoEvents 'allows calculations prior to performing
** avgNonZeros = 0 'default return
** For i = LBound(rangeList) To UBound(rangeList)
***** For Each cell In rangeList(i)
******** If cell <> 0 Then
*********** totSum = totSum + cell
*********** cnt = cnt + 1
******** End If
***** Next cell
** Next i
** If cnt <> 0 Then avgNonZeros = totSum / cnt
End Function
Bookmarks