Excel VBA code to Average Non Zeros for Multiple Ranges

**theclockmaker** · 03-11-2020, 01:00 PM

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

**BMV** · 03-11-2020, 01:37 PM

Function avgNonZeros(ParamArray rangeList() As Variant) As Double
'Returns the average for all nonzeros of rangeList.
'rangeList may be one or multiple ranges.
Dim Area As Variant
Dim totSum As Long
Dim cnt As Long
DoEvents 'allows calculations prior to performing
For Each Area In rangeList
    cnt = cnt + WorksheetFunction.Count(Area) - WorksheetFunction.CountIf(Area, "=0")
    totSum = totSum + WorksheetFunction.Sum(Area)
Next
If cnt <> 0 Then avgNonZeros = totSum / cnt
End Function

Excel VBA code to Average Non Zeros for Multiple Ranges

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Excel VBA code to Average Non Zeros for Multiple Ranges

Re: Excel VBA code to Average Non Zeros for Multiple Ranges

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Averageif, not counting zeros, but in multiple ranges)

How do I average multiple columns (ignoring zeros and blank cells)?

AVERAGE - Multiple VLOOKUPs whilst ignoring zeros or blanks

[SOLVED] Averaging across multiple ranges, not counting zeros, and the [h]:mm format

[SOLVED] how to average multiple averages that can contain zeros

[SOLVED] How do I get the average across multiple cells not in a range and not count the zeros/text

[SOLVED] Average for non-consecutive cells excluding zeros (even when they all contain zeros)

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions