Averaging Non-Zero Values

This page describes how to average a range of numbers, excluding zero values.

Introduction

There are (at least) three methods of averaging a range of numbers excluding zero values from the result. In all the examples below, we are averaging the range A1:A10.

Average With Array Formula

If you are familiar with array formulas, you can use a formula like the following:

    =AVERAGE(IF(A1:A10<>0,A1:A10,FALSE))
    

When entered with CTRL SHIFT ENTER rather than just ENTER, it will return the average of all non-blank, non-empty values in A1:A10. See Array Formulas for much more information on array formulas. This formula cannot be used with 3D sheet references.

Average With AVERAGEIF

In Excel 2007, the AVERAGEIF formula was introduced. This function is not available in Excel versions 2003 and earlier.

    =AVERAGEIF(A1:A10,"<>0")
    

This will average the non-zero values in the range A1:A10. This function cannot be used with 3D sheet references.

Average With AVERAGEIFS

In Excel 2007, the AVERAGEIFS formula was introduced. This function is not available in Excel versions 2003 and earlier.

This formula, as we use it here, is exactly the same as AVERAGEIF. In its full form, AVERAGEIFS can have multiple selection criteria. Here, we are using only a single selection criteria:

    =AVERAGEIFS(A1:A10,A1:A10,"<>0")
    

This will average the non-zero values in A1:A10.

Average From Multiple Sheets

If you need to average values from multiple sheets, you need some VBA code to do it. The code here, AverageNonZero, has the following declaration.

    Public Function AverageNonZero(FirstSheet As String, _
        LastSheet As String, _
        InDataRange As Range) As Variant
    

Here, FirstSheet is the name of the first (left-most) sheet to include in the average. LastSheet is the last (right-most) sheet to include in the average. All sheets between FirstSheet and LastSheet are included in the average. InDataRange specifies the range on each sheet that is to be included in the average. It doesn't matter what sheet this range resides on. The code gets the address of this range and applies that address to each included sheet. 

The function will return a #REF! error if FirstSheet does not exist, LastSheet does not exist, LastSheet is to the left of FirstSheet or InDataRange is Nothing. If no non-zero values are found, the function returns #DIV/0!.

The Code For AverageNonZero

The full code listing for the AverageNonZero is shown below:

  '>>>>>>>>> BEGIN CODE
Function AverageNonZero(FirstSheet As String, _
        LastSheet As String, _
        InDataRange As Range) As Variant
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' AverageNonZero
' By Chip Pearson, chip@cpearson.com www.cpearson.com
' This averages all non-zero cells on worksheets from FirstSheet
' to LastSheet in the range specified by InDataRange. InDataRange
' can reference a range on any worksheet -- the code uses the
' Address of the range to create the actual ranges on each sheet.
' Empty cells are assumed to have a value equal to zero thus are
' not included in the result. The Boolean values TRUE and FALSE
' are not included in the result.
'
' This function returns #REF if FirstSheet does not exist, if
' LastSheet does not exist, if LastSheet is to the left of
' FirstSheet, or InDataRange is Nothing. If no non-zero values
' are found, the result is #DIV/0!.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim WSStart As Long
Dim WSEnd As Long
Dim WSNdx As Long
Dim Addr As String
Dim R As Range
Dim Count As Long
Dim SumValues As Double
Dim SheetDataRange As Range

With ThisWorkbook.Worksheets
On Error Resume Next
Err.Clear
' Do some error checking.
WSStart = .Item(FirstSheet).Index
If Err.Number <> 0 Then
    AverageNonZero = CVErr(xlErrRef)
    Exit Function
End If
WSEnd = .Item(LastSheet).Index
If Err.Number <> 0 Then
    AverageNonZero = CVErr(xlErrRef)
    Exit Function
End If
If WSStart > WSEnd Then
    AverageNonZero = CVErr(xlErrRef)
    Exit Function
End If
If InDataRange Is Nothing Then
    AverageNonZero = CVErr(xlErrRef)
    Exit Function
End If

' Addr is the address of the range on each sheet to average.
Addr = InDataRange.Address
For WSNdx = WSStart To WSEnd
    ' Use Addr to get a range on Workshets(WSNdx).
    Set SheetDataRange = .Item(WSNdx).Range(Addr)
    For Each R In SheetDataRange.Cells
        If Len(R.Value) > 0 Then
            If IsNumeric(R.Text) Then
                If R.Value <> 0 Then
                    ' You can modify the line of code above to
                    ' include only certain values in the average.
                    ' This example averages only non-zero values,
                    ' but you can create any test you want.
                
                    ' Non-zero value found. Increment Count
                    ' and add to SumValues.
                    Count = Count + 1
                    SumValues = SumValues + R.Value
                End If
            End If
        End If
    Next R
Next WSNdx
End With
If Count = 0 Then
    ' No non-zero elements found.
    AverageNonZero = CVErr(xlErrDiv0)
    Exit Function
End If
' Average is SumValues / Count.
AverageNonZero = SumValues / Count
End Function
'>>>>>>>>> END CODE

You can download the bas file with all the example code on this page.

This page last updated: 20-Oct-2012.