Page 121 - Excel Workbook for Dummies

11_798452 ch06.qxp 3/13/06 7:48 PM Page 104
104 Part II: Using Formulas and Functions
Excel draws arrows showing the indirect dependents of the formula in cell C9.
Note that this includes a black dotted arrow line drawn from cell C9 to a graphic
showing a tiny worksheet. This is Excel’s way alerting you to the fact that there
are cells dependent on this one on other worksheets of the workbook to which
its error value has naturally spread.
11. Make the Ext Ref worksheet active and note that cell B3 that contains the linking
formula also now contains a #NAME? error value.

12. Switch back to the Formulas worksheet and there, reassign the range name,
source, to cell A2.
Note that the moment you rectify the source of the error (both literally and figu-
ratively), all the nasty #NAME? error values in the spreadsheet immediately dis-
appear along with all the arrows depicting the direct and indirect dependents.
13. Close the Formula Auditing toolbar and then close the Exercise6-6.xls workbook
file without saving your changes.

Dealing with Circular References

A circular reference in a formula is one that depends, directly or indirectly, upon its
own value. The most common type of circular reference occurs when you mistakenly
refer in the formula to the cell in which you’re building the formula itself. For exam-
ple, suppose that cell B10 is active when you build the formula:

=A10+B10

As soon as you complete this formula entry in cell B10, Excel displays an alert box
that says that it cannot calculate the formula due to the circular reference. As soon as
you press Enter or select OK to close this alert box, the program displays the Circular
Reference toolbar along with arrows tracing the formula’s precedents and dependents
and a help window on allowing or correcting a circular reference. The program also
inserts 0 in the cell with the circular reference and the indicator Circular followed by
the cell address with the circular reference appears on the Status bar.
Excel is not able to resolve this example of a circular reference in cell B10 because the
formula’s calculation depends directly upon the formula’s result, creating an endless
loop that continuously requires recalculating and can never be resolved.

Not all circular references are impossible for Excel to resolve. Some formulas contain
a circular reference that can eventually be resolved after many recalculations of its
formula (referred to as iterations) because, with each iteration of the formula, the
results get closer and closer to a resolution point.

Try It

Exercise 6-7: Solving a Circular Reference in a Formula
Open the Exercise6-7.xls workbook in your Chapter 6 folder inside the My Practice
Spreadsheets folder or on the Excel Workbook CD-ROM. This workbook contains an
income statement spreadsheet with a circular reference in cell B15 that Excel catches
the moment you open the file:
1. Read the text of the alert dialog box that appears the moment you open the
Exercise6-7.xls workbook and then select OK to close it.