Microsoft Office Tutorials and References
In Depth Information
How Excel Determines Calculation and Iteration Settings
To get a feel for how this works, open the example workbook presented in the previous section
(refer to Figure 16-4). Then perform the following steps:
1. Ensure the Enable Iterative Calculation check box is checked as described above.
2. Set the Maximum Iterations setting to 1.
3. Set the Maximum Change setting to .001.
4. Enter a different value into the Gross_Income cell (cell B1).
5. Press F9 to calculate the sheet.
Because the Maximum Iterations setting is 1, pressing F9 performs just one iteration. You’ll find
that the Contributions cell has not converged. Press F9 a few more times, and you’ll see the result
converge on the solution. When the solution is found, pressing F9 has no noticeable effect. If the
Maximum Iterations setting reflects a large value, the solution appears almost immediately
(unless it involves some slow calculations).
How Excel Determines Calculation
and Iteration Settings
You should understand that all open workbooks use the same calculation and iteration settings.
For example, if you have two workbooks open, you cannot have one of them set to automatic
calculation and the other set to manual calculation. Although you can save a workbook with
particular settings (for example, manual calculation with no iterations), those settings can change if
you open another workbook.
Excel follows these general rules to determine which calculation and iteration settings to use:
h The first workbook opened uses the Calculation mode saved with that workbook. If you
open other workbooks, they use the same Calculation mode.
For example, suppose you have two workbooks: Book1 and Book2. Book1 has its Iteration
setting turned off (the default setting), and Book2 (which uses intentional circular
references) has its Iteration setting turned on. If you open Book1 and then Book2, both
workbooks will have the iteration setting turned off. If you open Book2 and then Book1, both
workbooks will have their iteration setting turned on.
h Changing the Calculation mode for one workbook changes the mode for all workbooks.
If you have both Book1 and Book2 open, changing the Calculation mode or Iteration
setting of either workbook affects both workbooks.
h All worksheets in a workbook use the same mode of calculation.