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).