Excel: Controlling Iteration by Seeding Values in Circular RefLast reviewed: July 10, 1995Article ID: Q51972 |
The information in this article applies to:
SUMMARYYou 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:
MORE INFORMATIONFor example, to compute the value X, where X=1+6/X:
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
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |