Microsoft Office Tutorials and References
In Depth Information
Turn On Iterative Calculations
Chapter 4: Getting More Out of Formulas
The Excel Options dialog
4 Click Formulas.
5 Click to select the Enable
If Excel fails to converge
on the solution, you can
try typing a higher value
in the Maximum
Iterations text box.
If you want a more
accurate solution, you
can try typing a smaller
value in the Maximum
Change text box.
Note: The Maximum Change value
tells Excel how accurate you want
your results to be. The smaller the
number, the more accurate the
calculation, but the iteration takes
6 Click OK.
Excel performs the
The iterated result
appears in the formula
You see in this task that after you build your
formula, Excel displays circular reference arrows.
These arrows usually indicate an error, but not
in this case. When you set up an iterative
calculation, you are by definition setting up a
circular reference formula because there are
terms on the left and right sides of the equals
sign that depend on each other. In this section’s
example, the formula in C7 references the
Profit_Sharing cell, which is C6. However, the
Profit_Sharing cell references the Net_Profit cell,
which is C7, so the references are circular.
It can sometimes be useful to watch
the progress of the iteration one
step at a time. To set this up, follow
steps 1 to 4 to open the Excel
Options dialog box and display the
Formulas tab. Select the Manual
option, and type 1 in the Maximum
Iterations text box. Click OK to
return to your worksheet. Now, each
time you press F9, Excel performs a
single pass of the iteration.