Excel: Iterative Calc Returns Unexpected Value to Dependent WS

ID: Q52165

The information in this article applies to:

SUMMARY

If a Microsoft Excel worksheet is linked to another worksheet whose values are obtained through iteration, the values on the dependent sheet may not be the same as those on the independent sheet.


MORE INFORMATION

The following is an example:

  1. Cell A1 on Worksheet 1 is linked to cell B1 on Worksheet 2.


  2. Cell B1 on Worksheet 2 contains a formula such as "=B1+2", where the value is calculated using iteration.


  3. After calculation (iterating 10 times), cell B1 on Worksheet 2 contains the value 20.


  4. Cell A1 on Worksheet 1 now contains the value 18.


This is inherent in the use of iteration. When using iteration, Excel calculates each cell the defined number of times. In this example, cell A1 on Worksheet 1 is calculated before cell B1 on Worksheet 2; therefore, when the calculation is finished, cell A1 on Worksheet 1 will be one iteration behind.

Additional query words: 


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: March 15, 1999