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.
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.
Click the Formulas tab, click the Calculation Options button, and then click Manual .
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.
Press F9 to recalculate the worksheet. When you do, Excel displays a message box
indicating that you have created a circular reference.