Excel: Controlling Iteration by Seeding Values in Circular Ref

ID: Q51972



Microsoft Excel for Windows, version 4.x, 5.0, 5.0cMicrosoft Excel for the Macintosh, version 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.


  2. In cell A2, type =1+6/A1.


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). 


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: March 15, 1999