Part II: Working with Formulas and Functions
220
FIGURE 10.14If you see this warning, you know that the formula you entered will result in a circular reference.
When you get the circular reference message after entering a formula, Excel gives you two options:l (^) Click OK, and Excel displays a Help screen that tells you more about circular references.
l Click Cancel to enter the formula as is.
Regardless of which option you choose, Excel displays a message in the left side of the status bar to
remind you that a circular reference exists.
Warning
Excel won’t tell you about a circular reference if the Enable Iterative Calculation setting is in effect. You can
check this setting in the Formulas section of the Excel Options dialog box. If Enable Iterative Calculation is
turned on, Excel performs the circular calculation exactly the number of times specified in the Maximum
Iterations field (or until the value changes by less than 0.001 or whatever value is in the Maximum Change
field). In a few situations, you may use a circular reference intentionally. In these cases, the Enable Iterative
Calculation setting must be on. However, it’s best to keep this setting turned off so that you’re warned of circu-
lar references. Usually a circular reference indicates an error that you must correct. n
Usually, a circular reference is quite obvious and easy to identify and correct. But when a circular ref-
erence is indirect (as when a formula refers to another formula that refers to yet another formula that
refers back to the original formula), it may require a bit of detective work to get to the problem.
On the CD
The companion CD-ROM contains a workbook that demonstrates an intentional circular reference. This file is
named circular reference.xlsx.
Specifying when formulas are calculated .................................................................
You’ve probably noticed that Excel calculates the formulas in your worksheet immediately. If you
change any cells that the formula uses, Excel displays the formula’s new result with no effort on your
part. All this happens when Excel’s Calculation mode is set to Automatic. In Automatic Calculation
mode (which is the default mode), Excel follows these rules when it calculates your worksheet:l When you make a change — enter or edit data or formulas, for example — Excel
calculates immediately those formulas that depend on new or edited data.