Rolling Up Year-to-Date Totals
PSS ID Number: Q12221
Article last modified on 01-12-1989
PSS database name: D_MPlan

1.x 2.x 3.00 3.01 3.02 3.04 4.00 | 1.x    | 1.x      | 1.x

MS-DOS                           | CPM-80 | APPLE II | APPLE III


Summary:

The following article discusses accumulating values entered from one
cell into a separate cell. 

More Information:

Multiplan has the ability to take values entered into one cell and
accumulate then into a separate cell as a running or year-to-date
total using the iteration option, as follows: 

1. Start with the entry cell set to zero (0) (we will use R1C1 for our
   example). To the immediate right is R1C2, which we will use as the
   accumulator cell. 

2. In R1C2, enter the following formula:

      =IF(ISNA(ITERCNT()),RC,RC+RC[-1])

   This formula will produce a beep and a "Circular references
   unresolved" message on most computers and a "Can't resolve circular
   references" message on the Macintosh. Ignore this message for now. 

3. Move the cursor to a separate area of the spreadsheet (preferably
   an area that will not be in the printout). We will use R1C6. 

4. In R1C6, enter the formula "=ITERCNT()=1". Again, there will be a
   beep and a message about circular references. The cell will display
   "#N/A" (later, it will read "TRUE"). 

5. Go into the Options menu and set the Recalc field to No, the
   iteration field to Yes, and the completion test to R1C6.

Once the calculation and iteration options are set up, the
accumulator is ready to use. When you type a number into the entry
cell (R1C1, in our example) and recalculate the worksheet, the number
will be added to the number currently in the accumulator cell. 

When using this method, be careful when using the Recalc key
("Calculate Now" on the Macintosh), as the values will be added every
time recalculation is used, and one extra recalculation will throw
totals off. 

If some entries need to be updated without updating the accumulator
cell, put a 0 in the entry cell before recalculating to prevent the
previous entry from being added again. 

To set the accumulator cells to 0, do the following:

1. Go through the entry cells and enter the opposite of the value in
   the corresponding accumulator cells. For example, if an accumulator
   cell displayed "123.5", type "-123.5" in the entry cell. 

2. Recalculate once, then set the entry cells back to 0 or the new
   initial value. 

For more information about iteration, please refer to the "Microsoft
Multiplan" manual. 

Copyright Microsoft Corporation 1989.