Microsoft KB Archive/29811

Multiplan: Accumulating a Running Total in a Cell PSS ID Number: Q29811 Article last modified on 06-04-1990 PSS database name: D_MPlan

1.x 2.x 3.00 3.01 3.02 3.04 4.00 4.01 4.20

MS-DOS

Summary:

More Information:

To run a year-to-date total using iteration, start with the entry cell set to 0 (R1C1). To the immediate right is R1C2 (the accumulator cell). In R1C2, enter the following formula:

=RC+RC[-1]

This formula produces a “Circular references unresolved” message. After receiving one of these messages, move the cursor to a separate area of the spreadsheet (not the printout area). For this example, use R1C6. At R1C6, enter the following formula:

=ISNA(ITERCNT)

As before, there is a beep and a message about circular references. The cell displays TRUE.

Under the Options command, do the following:


 * 1) Set the “recalc:” field to No.
 * 2) Set the “iteration:” field to Yes.
 * 3) Set “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 and recalculate the worksheet, the number is added to the number that is currently in the accumulator cell.

When using this method, be aware that the Recalc key adds the current values every time it is used; one extra recalculation throws your totals off.

If only a few entries need to be updated, all cells that are not updated must be set to zero before recalculation. Do this to prevent the old entry from being added to the total again.

To set the accumulator cells to zero, go through the input cells and enter the opposite of the value in the corresponding accumulator cell. For example, if an accumulator cell displays 123.5, type -123.5 in the entry cell. Recalculate once, then set the entry cells back to zero or the initial value.

This method does not work if you are totaling the accumulator cells. If this is the case, enter the following formula at R1C2:

=ITERCNT=1

After entering the formula, set the completion test to read as follows:

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

Copyright Microsoft Corporation 1990.