Did you know that circular references can hide inside of formulas
and spring up unexpectedly? Here's an example you can test.
Consider the following standard usage of the IF
=IF(MyCond, MyFormula_1, MyFormula_2)
Now, what if a circular reference existed in
MyFormula_2. As long
as MyCond returns TRUE, we would have no idea there is a circular
reference in the spreadsheet.
This is interesting for more than one reason. Obviously, we need
to be careful when setting up an IF function so as not to create
circular logic. If the formulas are purely mathematical, we might
even consider using binary logic to accomplish the task (see the
"BinSwitch.zip" example file on the website). Binary logic would
mathematically process each MyFormula element so circular logic
would stand out.
As I mentioned, there is another side to this that is interesting.
If we should happen to actually need circular logic in a model
you could embed the circ ref within an IF function! This way, you
could actually create model switches to turn on/off circular
"Why is this exciting?", you may ask.
Remember, the downside to using circular logic is
that it throws
you into an iterations situation. You get the little "Calculate"
message at the botton of the Excel app. Once you've made the
commitment to use circular logic you've told the model to ignore
all circular formulas you create. That prevents us from knowing
if we accidentally create bad circular references.
Well (here's the neat part) if I embed all my circular
formulas within IF functions and tie all those IF's to a single
on/off switch, I can easily disable all the circular functions
in my model and turn off iterations momentarily to see if there
are any unexpected circular refs in the model!
AHAAAA! Perhaps I've finally got a way to "safely"
circ ref logic in my model. Now I guess I'll have to modify my
hard stance against circular reference logic in models.