HomeTechniques and Tips@RISK: General QuestionsCircular References

# 2.16. Circular References

Applies to:
@RISK 5.x–7.x
TopRank 5.x–7.x

How do @RISK and TopRank deal with circular references?

@RISK and TopRank are fully able to cope with them, if you have set Excel's option to perform iterative calculations:

• Excel 2010 and later, File » Options » Formulas » Enable iterative calculation.
• Excel 2007: click the round Office button and then Excel Options » Formulas » Enable iterative calculation.
• Excel 2003: Tools » Options » Calculation » Iteration

For more about appropriate settings for this option, see Microsoft's Knowledge Base article Make a circular reference work by changing the number of times that Excel iterates formulas (accessed 2015-06-08, part of Remove or allow a circular reference).

Microsoft provides more detailed information about circular references, including troubleshooting tips and a tutorial on iterative calculation in a different Microsoft Knowledge Base article with the same title: Remove or allow a circular reference (accessed 2015-06-08).

How @RISK and TopRank respond to circular references:

During precedent tracing: When @RISK or TopRank hits a cell that has been previously encountered, it stops tracing precedents in that particular path, to avoid an infinite loop. For example, if A1 depends on B1, and B1 on C1, and C1 on A1, and the program starts tracing at A1, it will find B1 and C1 as precedents, but stop tracing when it hits A1 again. However, when it starts tracing precedents of B1, it will find C1 and A1 as precedents, and so forth. The net result is that when there is a circular reference, @RISK and TopRank treat all members of the circle as precedents of each other.

During calculation: If there are circular references, Excel calculates the model multiple times within each @RISK or TopRank iteration, depending on your Excel settings for circular references. Each @RISK or TopRank function returns the same value through all the recalculations within any given iteration. In other words, Excel recalculations to resolve circular references all use the same samples within any one iteration of @RISK or TopRank.

Last edited: 2015-06-08