ACC: How to Exclude Zero Values When You Calculate Averages

Article translations Article translations
Article ID: 109364 - View products that this article applies to.
This article was previously published under Q109364
Moderate: Requires basic macro, coding, and interoperability skills.
Expand all | Collapse all

On This Page

SUMMARY

When you use the Avg() function in a report to average a set of values, the function uses records that contain zero values in the calculation. In some cases you may not want to include records with zero values in a calculation.

This article includes two examples of how to calculate an average for all the non-zero values in a set by counting the number of non-zero values in the set of values and then using that total with a running sum calculation.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0.

MORE INFORMATION

Example One

In this example, one text box displays the number of non-zero values, and the other text box displays the average for the set:

  1. Open the sample database Northwind.mdb (or NWIND.MDB in version 1.x or 2.0).
  2. Use a Report Wizard to create a new Groups/Totals report based on the Order Details table. This report will calculate the average discount for each Product ID.
  3. Include the ProductID and Discount fields on the report. Group the report on the ProductID field, grouped as Normal.

    NOTE: In versions 1.x and 2.0, there is a space in the Product ID field name.
  4. Open the report in Design view.
  5. Add an unbound text box to the report's detail section. Position this text box to the left of the Discount text box. The new text box will display the count of non-zero values. Set the text box's ControlSource property to:
          =IIf([Discount]=0 or [Discount] is null,0,1)
    						
    This expression returns 0 if the value of the Discount field is equal to zero or Null; otherwise, it returns 1. The Avg() function automatically excludes nulls as well.
  6. Set the text box's RunningSum property to Over Group and its Name property to CountOfData.

    NOTE: In version 1.x, the Name property is called the ControlName property.
  7. Add another text box to the report's footer section. This text box will display the result of the average calculation.
  8. Set the ControlSource property of this text box to:
          =Sum([Discount])/[CountOfData]
    						
  9. Preview the report.
The left column displays a running count of non-zero (and non-null) Discounts and the group footer displays an average Discount based on the running count.

Example Two

This example uses a user-defined function that is the functional inverse of the NullToZero() function in the Northwind sample database:

  1. Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).
  2. Create a module and type the following line in the Declarations section if it is not already there:
    Option Explicit
  3. Type the following procedure:
          Function ZeroToNull( MyValue As Variant) As Variant
             If MyValue = 0 Or MyValue = Null Then
                ZeroToNull = Null
             Else
                ZeroToNull = MyValue
             End If
          End Function
    						
  4. Repeat the steps in Example One, but use the following expression in place of the expression in step 5:
          =ZeroToNull([Discount])
    						
    You can also use the ZeroToNull() function in a query.

REFERENCES

For more information about the Avg() function, search the Help Index for Avg function.

Properties

Article ID: 109364 - Last Review: January 18, 2007 - Revision: 2.3
APPLIES TO
  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition
Keywords: 
kbhowto kbprogramming kbusage KB109364
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com