Excel: Controlling Iteration by Seeding Values in Circular Ref

Last reviewed: July 10, 1995
Article ID: Q51972

The information in this article applies to:

  • Microsoft Excel for Windows, versions 4.x, 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 1.x, 2.x, 3.0, 4.0, 5.0a

SUMMARY

You can use circular references with iteration to compute such things as simultaneous equations and complex recursive formulas. In computing these circular references, it is sometimes necessary to "seed" the iteration with a starting value to prevent calculation errors (such as #DIV/0!) from occurring based on initial values of the cells.

To seed the calculation, do the following:

  1. Replace one of the formulas with a value.

  2. Perform a Calculate Now (if calculation is set to Manual) to force the other cell to calculate to a number other than the original value.

  3. Re-enter the formula that was replaced.

MORE INFORMATION

For example, to compute the value X, where X=1+6/X:

  1. In cell A1, type "=A2" (without the quotation marks).

  2. In cell A2, type "=1+6/A1" (without the quotation marks).

This creates a circular reference. To calculate, calculation must be set with iterations. However, at this time, cell A1 has the value 0 in it, and cell A2 has the error #DIV/0!. To correct this problem:

  1. In cell A1, type "2" (without the quotation marks--replace the formula with this entry).

  2. If calculation is set to Manual, click the Calc Now button (on the Calculation tab of the Options dialog box). (Or if you are using a version of Microsoft Excel earlier than version 5.0, choose Calculate Now from the Options menu.)

    The value 4 will now be in cell A2.

  3. Re-enter the formula "=A2" in cell A1.

Microsoft Excel uses the starting value of 4 rather than 0 (zero), and the formula is calculated correctly.

When you seed values for iterations, you should understand the order in which Microsoft Excel performs calculations. After the last value in a circular reference is entered, Microsoft Excel calculates the values by searching through the worksheet (beginning at cell A1 and proceeding through the worksheet left to right, top to bottom) for dependent cells.

If, searching in this manner, Microsoft Excel encounters other cells that are dependent on the cell you just entered before it encounters the newly entered cell, it recalculates that dependent cell as if the value of the newly entered cell is 0. Once Microsoft Excel reaches the newly entered cell, it calculates the value of the newly entered cell based on the new value of the dependent cell.

The following is an example of this calculation process:

Using the scenario above, you seed the formula by setting a value to cell A1 and then changing A1 back to a formula to complete the circular reference. This works because A1 is the first cell Microsoft Excel encounters in recalculating the iteration; thus, it uses the existing values for the other cells in the circular reference. If, however, you place the formula =A1 in cell A2 and =1+6/A2 in cell A1 and then try to seed A2 with the number 2, you again encounter the #DIV/0! error because Microsoft Excel calculates as follows:

   When you place the number 2 in cell A2, Microsoft Excel calculates
   cell A1 and return 4 (as before). However, when you then change A2
   to the formula =A1, Microsoft Excel starts at the beginning of the
   worksheet and first find cell A1. To calculate the value of A1,
   Microsoft Excel takes the current value of A2 which, in this case,
   is 0, not 4 (because the formula in A2 has been entered but not yet
   calculated). This yields a #DIV/0! error in cell A1 which, of
   course, is then carried into cell A2, leaving you again in an
   error loop.

Thus, it is important to seed the value for the iteration in the first cell Microsoft Excel calculates (that is, the first one encountered searching left to right, top to bottom).


KBCategory: kbusage
KBSubcategory:

Additional words: 1.00 1.03 1.04 1.06 1.50 2.20 3.00 4.00 5.00 5.00a 5.00c


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: July 10, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.