Circular Reference

[Circular Reference Problem]

I am trying to calculate what I think is a Circular
Reference but am not receiving any detail of a "Circular
Reference" when going
into "View" "Toolbars" "Options" "Circular Reference".
What I want to do will not calculate and I have #REF!
appearing in cells? I have been into "Tools" "Options"
and found that "Maximum Iterations" was already checked
to 100.

When trying to calculate financial analysis I have a
column calculating an account balance based on an income
stream and transactions coming and going - listed in
separate colums. The problem appears when trying to
calculate the difference in tax payable based on adding
and subtracting these transactions from the income source
and re-calculating the new tax payable. First of all, I
have the account balance adding all income and
subtracting all expenses and the old tax payable (based
on original income with no income or expenses). I have
another calcualtion including all income and expenses
with a resulting total of the income stream. Tax payable
is calculated on this new income result and the
difference deducted from the original tax paid. The
result in this cell is then added to the account balance
and I think this is what is causing the #REF! result.
When trying to locate the problem I am going round in
circles with all cells appearing to calculate correctly -
can anyone help?

 

[John Tjia]

The #REF is probably the result of an error that you have since
cleared up, but as you have a (deliberate) circular reference, this
message is now caught in a loop. What you have to do to clear this
out is break the loop make by the circular reference.

You can do it in one of two ways:

1. Take one of the simpler lines in your model and delete it. Better
yet, copy it out off to the side, delete the original formulas. Press
F9 to recalc if you are not on automatic calculation. The #REF should
disappear if the source of the error has been corrected. Then copy
back the formulas into the original position.

2. On one of the lines in your model, insert an error trap. For
example, choose a line that has, say, =D20+D21. Turn this into
=IF(ISERROR(D20+D21),0,D20+D21). What this does is that when the
formula returns a #REF, the formula flips to a 0, and effectively
breaks the loop. As the iteration continues and clears the #REF out,
the formula no longer returns a #REF and flips back to the
calculation. It's an automatic error-trapping.