When does a model have a circular reference?

One of the checks that Openbox does during Validation is whether a model has a circular reference – that is, a calculation that ultimately depends on itself.

Excel also checks for circular references, but it doesn’t always find a circularity that Openbox says is there. Why?

In short, Excel takes into account the current model inputs, whereas Openbox does not – because they might change. 

Suppose you have a formula like = IF( J10 = 1, J8, J9 )

Also, suppose that J8 is part of a circular reference, but J9 is not.

In this case, Excel will report a circularity only if J10=1. That is, it will only report a circularity that is actually a problem now. It only considers the “live” part of the formula with IF.

Openbox, on the other hand, will report a circularity whatever the input values. So even if J10= 0, the model still counts as circular for Openbox.

We feel this is a more robust approach to circularity. Unless you can guarantee that J10 will never be 1, then the model is potentially circular and you should be aware of that. And if J10 is never 1, why have the IF function in the first place?

However, you can choose to ignore Openbox’s message and continue to the preview window. In that case, Openbox will check for circularity using the same approach as Excel, and warn you if it finds one.

Obviously if Openbox creates the model in Excel, Excel’s circularity detection rules will apply.