Knowledge Base

Works: How to Calculate a Running Total/Average in a Database

Article ID: 124647

Article Last Modified on 11/15/2004

APPLIES TO

This article was previously published under Q124647

SUMMARY

A running total or average displays the sum or average of all the values in a specific field up to any given record. In Microsoft Works, you can keep a running total and a running average by using self-referencing formulas. The table below resembles a database that contains running totals and averages, in which blank values are not counted in the average. 

   Values  Number   Total  Average
   -------------------------------

     20      1        20     20
     40      2        60     30
             2        60     30
     60      3       120     40
     80      4       200     50
    100      5       300     60


To create a sample database similar to the one in this article, use the steps in the following procedure.

MORE INFORMATION

To create a table resembling the above example, do the following:

  1. In a new database, create 4 fields: Values, Number, Total, and Average.
  2. Switch to List view.
  3. In Record 1, type the following data (press ENTER after each entry):

    1. In the Values field, type 20.
    2. In the Number field, type 1.
    3. In the Total field, type the number located in Values.
    4. In the Average field, enter the following formula:

      =Total/Number

  4. In Record 2, enter the following data:

    1. In the Values field type, 40.
    2. In the Number field, enter the following formula:

      =Number+IF(Values,1,0)

    3. In the Total field, enter the following formula:

      =Total+Values

  5. Continue entering numbers above in the Values field.

    At this point, every value entered in the Values field will automatically place the correct values in the following 3 fields.
A running total will be calculated in the Total field while a running average will be calculated in the Average field.

Additional query words: 2.00a 3.00 3.00a 3.00b 4.00 4.50 4.50a w_works recursion accumulative cumulative recursive

Keywords: kbhowto kbfaq KB124647


Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.