Microsoft Office Tutorials and References

In Depth Information

**Turn On Iterative Calculations**

Chapter 4: Getting More Out of Formulas

The Excel Options dialog

box appears.

4
Click Formulas.

5
Click to select the Enable

Iterative Calculation

option.

4

5

If Excel fails to converge

on the solution, you can

try typing a higher value

in the Maximum

Iterations text box.

●

6

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

longer.

6
Click OK.

●

Excel performs the

iteration.

The iterated result

appears in the formula

cell.

●

Important!

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.

Try This!

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.