XL2000: Using Iterations and Maximum Change in Calculations

This article contains information about two of the calculation settings for Microsoft Excel 2000: iteration and maximum change. This article also includes information about how the settings affect calculations when you use circular references in formulas.

Iteration is the process of repeatedly calculating values in a worksheet until a specific numeric condition is met. In Microsoft Excel, the condition is the limit you set in the Maximum iterations box on the Calculation tab in the Options dialog box. (To see the Options dialog box, click Options on the Tools menu).

Maximum change is the maximum amount of change you will accept between calculation results. To set this change value, type the number in the Maximum change box on the Calculation tab of the Options dialog box. The smaller the number, the more accurate the result and the more time Microsoft Excel requires to calculate a worksheet.

When Microsoft Excel calculates values in the worksheet, it repeats calculations until it reaches the number of iterations you set in the Maximum iterations box or until it changes all cells by less than the amount you set in the Maximum change box, whichever is reached first. The default value of iterations is 100 and the default value for maximum change is .001.

If the change in the result is greater than or equal to the value in the Maximum change box, Excel continues to calculate as long as the limit in the Maximum iterations box has not been reached. If the change in the result is less than the maximum change value, Excel stops calculating.

The two calculation settings, Maximum iterations and Maximum change are only enabled for that session of Excel. When you quit and restart Excel, the options return to their default settings. However, you can save a workbook that has custom calculation settings, so that when you open that workbook, the settings exist for the entire session, even if you close that workbook.

Example 1

In this example the maximum change is smaller than the result of each calculation so that the calculations will continue until you have reached the maximum number of iterations.

1. In a new workbook, on the Tools menu, click Options. On the Calculation tab, click Manual, and then click Iteration.
2. In the Maximum iterations box, type 100 and in the Maximum change box, type 1, and then click OK.
3. Click cell A1 and type the following circular formula:
   =A1+1
   The value "1" is returned in cell A1.
4. Press the F9 key to recalculate the workbook.

Note that the value in cell A1 is 101 and each successive calculation increments the result by 100.

This is because each incremental change is never less than the maximum change value of 1. The circular formula adds 1 to the result. Because the maximum iteration value is 100, each iteration adds 1 to the value, which results in 1x100.

Example 2

This example is a continuation of example 1. In this example, the maximum change is larger than the result of each iteration so that the calculation stops after only one iteration.

1. On the Tools menu, click Options. On the Calculation tab, click Manual, and then click Iteration
2. In the Maximum change box, type 1.001, and then click OK.
3. Press F9 to recalculate the workbook.

The result is incremented by 1 instead of by 100.

Example 3

In this example only one iteration is allowed for each calculation.

1. Create a new workbook. On the Tools menu, click Options. On the Calculation tab, click Manual, and then click Iteration.
2. In the Maximum iterations box, type 1, and in the Maximum change box, type 0.001, and then click OK.
3. Enter the following in Sheet1:

      A1: 1000
      A2: =(A1+A2)/10
   						

   The initial result of the formula in cell A2 when you press ENTER is 100, as expected.
4. Press F9 seven times. The resulting value increments as follows:
   110
   111
   111.1
   111.11
   111.111
   111.1111
   111.1111
   
   Notice that the formula did not recalculate when you pressed F9 the seventh time. That is because the previous iteration was ".0001" which is less than the value entered as the maximum change. Pressing F9 again will not change the final result. (The limit of 15 significant digits in Excel applies).

Example 4

NOTE: This example is a continuation of Example 3; you must follow the steps for Example 3 before you follow the steps for this example.

1. On the Tools menu, click Options. On the Calculation tab, click Manual, and then click Iteration.
2. In the Maximum iterations box, type 100, and in the Maximum change box, type .001, and then click OK.
3. Enter the following formula in cell D1:
   =D1+1
4. Retype the formula in cell A2 as follows:
   =(A1+A2)/10
5. Press F9 to recalculate the formulas in the workbook.
   
   Excel repeats the calculation 100 times.

This behavior occurs because the amount by which the value in cell D1 changes always exceeds the limit you set in the Maximum change box (the change in the result remains above (.001). In this case, Excel recognizes at least one formula in which the limits for halting calculation are not met and Excel continues to calculate the formula.

By definition, a circular reference is never completed. Excel calculates all circular reference formulas until they meet either the maximum iteration or the maximum change limits.