ACC2000: How to Exclude Zero Values When You Calculate Averages

When you use the Avg() function in a report to average a set of values, the function uses records that have zero values in the calculation. Sometimes, you do not want to include records with zero values in a calculation.

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

Example One

In this example, one text box will display the number of nonzero values, and the other text box will display the average for the set:

1. Open the sample database Northwind.mdb.
2. In the Database window, click Reports, and then click New.
3. In the New Report dialog box, click Report Wizard, and then click OK.
4. Use the Report Wizard to create a new Groups/Totals report based on the Order Details table. This report calculates the average discount for each product ID.
   
   Include the ProductID and Discount fields on the report.
5. Click Next in the Report Wizard, and then click Grouping Options.
6. In the Group-level fields box, type ProductID; in the Grouping intervals box, click Normal.
7. Click OK, and then click Finish.
8. On the View menu, click Design View.
9. Add an unbound text box to the Detail section of the report. Position the text box to the left of the Discount text box. The new text box will display the count of nonzero values. Set the ControlSource property of the text box 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 Null values as well.
10. Set the RunningSum property of the text box to Over Group, and set its Name property to CountOfData.
11. Add another text box to the Report Footer section. This text box will display the result of the average calculation.
12. Set the ControlSource property of this text box to:
    =Sum([Discount])/[CountOfData]
13. Preview the report.

The left column displays a running count of nonzero (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.
2. Create a module, and then 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 the "Example One" section, but use the following expression in place of the expression in Step 9:
   =ZeroToNull([Discount])

NOTE: You can also use the ZeroToNull() function in a query.

For more information about the Avg() function, click Microsoft Access Help on the Help menu, type avg function (microsoft jet sql) in the Office Assistant or the Answer Wizard, and then click Search to view the topic.