KB127859 - Works: Creating a Running Total in Works for Windows

Article ID: 127859

Article Last Modified on 11/15/2004

Microsoft Works 4.0 Standard Edition
Microsoft Works 4.5 Standard Edition
Microsoft Works 4.5a
Microsoft Works 2.0 Standard Edition
Microsoft Works 2.0a
Microsoft Works 3.0 Standard Edition
Microsoft Works 3.0a
Microsoft Works 3.0b

This article was previously published under Q127859

SUMMARY

In a Microsoft Works Spreadsheet, you can use formulas to automatically create and calculate running totals. The methods described here work without circular references. A circular reference is where one cell in a spreadsheet formula refers to either itself or to another cell that references back to the original cell. Circular references should be avoided because they might cause the spreadsheet formula to recalculate or perform a function repeatedly, causing incorrect results.

MORE INFORMATION

        A          B         C          D            E

   1   Date    Bells Sold  Total   Whistles Sold   Total
   2  1/2/95       20       20          15          15
   3  1/9/95       10       30           5          20
   4 1/16/95       17       47           7          27
   5 1/23/95       45       92          25          52
   6 1/30/95       12      104          10          62
   7  2/6/95       17      121           6          68

The design above automatically keeps running totals in the last cells in columns C and E and provides a column to track the dates amounts were entered. This design eventually grows in size (down the page).

The formulas for the above spreadsheet are:

        A          B         C          D             E

   1   Date    Bells Sold  Total   Whistles Sold    Total
   2                         =B2                      =D2
   3                      =B3+C2                   =D3+E2
   4                      =B4+C3                   =D4+E3
   5                      =B5+C4                   =D5+E4
   6                      =B6+C5                   =D6+E5
   7                      =B7+C6                   =D7+E6

Or, using the SUM function:

        A          B         C               D             E

   1   Date    Bells Sold  Total        Whistles Sold    Total
   2                      =SUM($B$2:B2)                 =SUM($D$2:D2)
   3                      =SUM($B$2:B3)                 =SUM($D$2:D3)
   4                      =SUM($B$2:B4)                 =SUM($D$2:D4)
   5                      =SUM($B$2:B5)                 =SUM($D$2:D5)
   6                      =SUM($B$2:B6)                 =SUM($D$2:D6)
   7                      =SUM($B$2:B7)                 =SUM($D$2:D7)

The advantage of using the SUM function, as in the above example, is that you would only have to enter the function once for each Totals column, and then use the Fill Down command from the Edit menu to copy the function as far as needed. Additionally, if you wanted a running total of two categories, you could combine the two cell ranges in one SUM function. For example, to get the Total Bells plus Total Whistles sold, you could use the formula =SUM($B$2:B2,$D$2:D2).

Another running total method maintains the spreadsheet size, avoiding growth downward, but is not automated and does not tell you when information changed. For example:

          A           B            C           D

   1               Current     Previous     New Total
   2    bells        23           20           43
   3    whistles     52           42           94
   4    pipes        75           25          100
   5    cigars       12           12           24

The formulas are:

          A           B            C           D

   1               Current     Previous     New Total
   2    bells                                =B2+C2
   3    whistles                             =B3+C3
   4    pipes                                =B4+C4
   5    cigars                               =B5+C5

To update the running total with this formula, you need to do the following:

In the "New Total" column (cells D2 to D5), select the entries.
From the Edit menu, choose Copy.
Select cell C2, and choose Paste Special from the Edit menu.
In the Paste Special dialog box, select Values Only. Choose OK.
Enter the new Current values in column B.

For additional information about circular references in the Works spreadsheet module, please see the following article in the Microsoft Knowledge Base:

79970 Troubleshooting Circular References in Works in Windows

Additional query words: 2.00 2.00a 3.00 3.00a 3.00b 4.00 4.50 4.50a kbhowto w_works sum macro macros

Keywords: kbhowto kbfaq KB127859