This Page: www.cpearson.com/excel/NonZeroAverage.aspx

Last Updated: 06-Nov-2013

Copyright 1997 - 2014, Charles H. Pearson

Site Last Updated: 05-Apr-2017

Advanced Software

Design And Development

Design And Development

Office Integration Projects
NET Programming
XML Development

Search The Site:

Averaging Non-Zero Values

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

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.

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.

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.

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.

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 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. |