Microsoft Office Tutorials and References

In Depth Information

**Working with iterative calculation options and automatic workbook calculation**

▪
Manual
Requires you to press
F9
or click the
Formulas
tab and click the
Calculate

Now
button to recalculate your worksheet

In the Calculation Options section, you can also choose to allow or disallow iterative

calculations. Selecting the Enable Iterative Calculation check box lets Excel repeat calculations for

cells that contain formulas with circular references. The default Maximum Iterations value

of 100 and Maximum Change of 0.001 are appropriate for all but the most unusual

circumstances. Click OK to accept your changes.

TIP
You can also have Excel recalculate its formulas by clicking the Formulas tab on the

ribbon, clicking the Calculation Options button, and selecting the behavior you want.

3

In this exercise, you’ll create a formula that has a circular reference and then change the

program’s iterative calculation options to find the result.

SET UP
You need the SavingsIncentive workbook located in the Chapter03 practice file

folder to complete this exercise. Open the workbook, and then follow the steps.

1

Click the
Formulas
tab, click the
Calculation Options
button, and then click
Manual
.

2

In cell
B6
, enter the formula
=B7*B9
and press
Enter
to display the initial result of the

formula, which is
$1,600.00
. Note that this result is incorrect because the Gross Savings

minus the Savings Incentive should equal the Net Savings value, which it does not.

3

Press
F9
to recalculate the worksheet. When you do, Excel displays a message box

indicating that you have created a circular reference.