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.