Microsoft Office Tutorials and References

In Depth Information

**Goal Seeking**

When you enter a formula that contains a circular reference, Excel displays a dialog box with two

options: OK and Cancel.

Normally, you’ll want to correct any circular references, so you should click OK. After you do so,

Excel inserts tracing arrows and displays the Help topic for circular references. The status bar

displays
Circular References: A3
in this case. To resolve the circular reference, choose
,

Formulas

Circular References to see a list of the cells

involved in the circular reference. Click each cell in turn and try to locate the error. If you cannot

determine whether the cell is the cause of the circular reference, navigate to the next cell on the

Circular References submenu. Continue reviewing each cell on the Circular References submenu

until the status bar no longer reads
Circular References
.

➜

Formula Auditing

➜

Error Checking

➜

In a few situations, you may want to use a circular reference intentionally. Refer to

Chapter 16 for some examples.

Instead of navigating to each cell using the Circular References submenu, you can click

the tracer arrows to quickly jump between cells.

If you ignore the circular reference message (by clicking Cancel), Excel enables you to enter the

formula and displays a message in the status bar reminding you that a circular reference exists. In

this case, the message reads
Circular References: A3
If you activate a different worksheet
.

or workbook, the message simply displays
Circular References
(without the cell reference).

Excel doesn’t warn you about a circular reference if you have the Enable Iterative

Calculation setting turned on. You can check this in the Excel Options dialog box (in the

Calculation section of the Formulas tab). If this option is checked, Excel performs the

circular calculation the number of times specified in the Maximum Iterations field (or

until the value changes by less than .001 — or whatever other value appears in the

Maximum Change field). You should, however, keep the Enable Iterative Calculation

setting off so that you’ll be warned of circular references. Generally, a circular reference

indicates an error that you must correct.

When the formula in a cell refers to that cell, the cause of the circular reference is quite obvious

and is, therefore, easy to identify and correct. For this type of circular reference, Excel does not

show tracer arrows. For an indirect circular reference, like in the preceding example, the tracer

arrows can help you identify the problem.

Goal Seeking

Many spreadsheets contain formulas that enable you to ask questions, such as, “What would be

the total profit if sales increase by 20 percent?” If you set up your worksheet properly, you can

change the value in one cell to see what happens to the profit cell.