Microsoft Office Tutorials and References
In Depth Information
Intentional Circular References
About circular references
For a practical, real-life demonstration of a circular reference, see the sidebar “More about
circular references,” later in this chapter.
Figure 16-2: The Circular Reference command displays a list of cells involved in a circular reference.
Understanding indirect circular references
Often, finding the source of a circular reference is easy to identify and correct. Sometimes,
however, circular references are indirect. In other words, one formula may refer to another formula
that refers to a formula that refers back to the original formula. In some cases, you need to
conduct a bit of detective work to figure out the problem.
For more information about tracking down a circular reference, see Chapter 21.
Intentional Circular References
As mentioned previously, you can use a circular reference to your advantage in some situations.
A circular reference, if set up properly, can serve as the functional equivalent of a Do-Loop
construct used in a programming language, such as VBA. An intentional circular reference introduces
recursion into a problem. Each intermediate “answer” from a circular reference calculation
functions in the subsequent calculation. Eventually, the solution converges to the final value.
By default, Excel does not permit iterative calculations. You must explicitly tell Excel that you
want it to perform iterative calculations in your workbook. You do this by selecting the Enable
Iterative Calculation check box in the Formulas section of the Excel Options dialog box (see
Figure 16-3).
Search JabSto ::

Custom Search