Microsoft KB Archive/12221

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:

  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.   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.   Move the cursor to a separate area of the spreadsheet (preferably an area that will not be in the printout). We will use R1C6.   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”).   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.