XL97: Using Iterations and Maximum Change in Calculations

Article translations Article translations
Article ID: 177856 - View products that this article applies to.
This article was previously published under Q177856
Expand all | Collapse all

On This Page

SUMMARY

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

MORE INFORMATION

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

To specify the maximum amount of change you will accept between calculation results, type the amount in the Maximum Change box. The smaller the number, the more accurate the result and the more time Microsoft Excel requires to calculate a worksheet.

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

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. Unless you change the default iteration settings, Excel stops calculating after 100 iterations or when all calculated values change by less than 0.001 (the default maximum change value) between iterations. During iteration, values move closer to the correct solution with each iteration. This is called "convergence."

If the change in the result, or the delta, 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 result is less than the maximum change value, Excel stops calculating.

Example 1: Large Iterations Value

To see an example of this calculation methodology, follow these steps:
  1. In a new workbook, click Options on the Tools menu. Click the Calculation tab and click Manual. Click Iteration. In the Maximum Iterations box, type 100. In the Maximum Change box, type 1. Click OK.
  2. Click cell A1 and type the following circular formula:
    =A1+1
  3. The value 1 is returned in cell A1. Press the F9 key to recalculate the workbook.

    Note that the value in cell A1 is 101. 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 1*100.
  4. Change the value in the Maximum Change box from 1 to 1.0001 and press F9 to recalculate the workbook. The result is incremented by 1 instead of by 100.

    This behavior occurs because the value returned by the first calculation reaches the maximum change limit you set (that is, the change in result is less than the value you specified in the Maximum Change box).

Example 2a: Small Iterations Value

To see this example, follow these steps:
  1. Create a new workbook. On the Tools menu, click Options. Click the Calculation tab and click Manual. Click Iteration. In the Maximum Iterations box, type 1. In the Maximum Change box, type 0.001. Click OK.
  2. 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, or (1000+0)/10=100.

    Press F9. The resulting value is 110, or (1000+100)/10=110. The change in result is 10.

    Press F9 again, the resulting value is 111, or (1000+110)/10=111. The change in result is 1.

    Press F9 again, the resulting value is 111.1, or (1000+111)/10=111.1. The change in result is 0.1.

    Press F9, the resulting value is 111.11, or (1000+111.1)/10=111.11. The change in result is 0.01.

    Press F9, the resulting value is 111.111, or (1000+111.11)/10=111.111 The change in result is 0.001, which is equal to but not less than the value you specified in the Maximum Change box (0.001).

    Press F9, the resulting value is 111.1111, or (1000+111.111)/10=111.1111 In this case the change in result is 0.0001, which is less than the value you specified in the Maximum Change box. We would expect Excel to stop calculating if you set maximum iterations above 6.
If you set maximum iterations to 100, and you reenter the formula in cell A2, the initial resulting value in A2 is 100. When you press F9, the resulting value is 111.1111 (as expected). When the workbook is recalculated again, Excel calculates once, and then halts because the change in result after first calculation is less than the maximum change value (The limit of 15 significant digits in Excel applies).

Example 2b

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

To see this example, follow these steps:
  1. On the Tools menu, click Options. Click the Calculation tab and click Manual. Click Iteration. In the Maximum Iterations box, type 1. In the Maximum Change box, type 0.001. Click OK.
  2. Type the following in cell D1:
    =D1+1
  3. Retype the formula in cell A2 as follows:
    =(A1+A2)/10
  4. 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 never exceeds the limit you set in the Maximum Change box (the change in the result remains below 0.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. Since, 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.

Properties

Article ID: 177856 - Last Review: November 20, 2001 - Revision: 1.0
APPLIES TO
  • Microsoft Excel 97 Standard Edition
Keywords: 
kbhowto kbui KB177856
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com