Problem when blanking out cells containing circular reference

**Liquidus** · 11-09-2011, 07:39 PM

Hello, I have a spreadsheet containing cells that contain a circular reference. I've also added the functionality to blank out these cells using IF(*criteria to blank*, "", *value if false*). The criteria to blank depends on a number I've entered into another cell.

I am having a problem where if I blank out these cells, and then unblank them, I get a #VALUE! error from the cells with circular references. However, if I go back into these cells, click the formula, and press enter, the cell will fix itself and the correct number will be displayed again. If there a way I can skip doing this and have the cells automatically populate themselves? Pressing F9 does not fix this problem. Thank you.

**6StringJazzer** · 11-09-2011, 08:53 PM

This is why Excel warns against circular references. Why do you have them?

**Liquidus** · 11-09-2011, 09:22 PM

My spreadsheet is a 10 year income statement for an apartment building. Among other expenses, there is a property tax expense which is based on a percentage of net income. This causes a circular reference where the property tax decreases net income which decreases property tax which decreases net income and etc. I'm not sure how Excel comes to a final number but it seems to work.

I've added the functionality to blank out certain years of the income statement using the IF statement described above. However, that causes the property tax and net income cells to have a #VALUE! error. If I go back into the formula for net income and just press enter, it fixes itself. I would like some way to skip having to reenter the formula in each Net Income cell.

**6StringJazzer** · 11-09-2011, 10:49 PM

I believe this calculation can be performed without a circular reference, which would be a better practice. Normally a circular reference is considered an error, but there are ways to use them constructively and in a controlled fashion. For example, it is possible to specify a maximum number of iterations. But then you can get problems like yours.

I think circular references are useful if you are trying to get a formula to iteratively converge, like solving a non-linear equation, although Solver can do that kind of thing too.

I would suggest reworking the formulas to avoid this but I can't offer a solution without seeing your file.

http://office.microsoft.com/en-us/ex...005200285.aspx

**6StringJazzer** · 11-09-2011, 10:52 PM

Originally Posted by Liquidus

...there is a property tax expense which is based on a percentage of net income.

By the way, I am not familiar with commercial property taxes but I would think that the tax is calculated on pre-tax net income. It would be crazy to require taxpayers to do iterative calculations to do their taxes!

**Liquidus** · 11-10-2011, 03:11 PM

Apparently, the local government bases the property tax on market value of the building, which is based on net income, which includes property taxes. However, I realized that I can just calculate a net income using last year's property taxes. The difference is marginal and it has solved the circular reference problem. Thanks for the help.

**6StringJazzer** · 11-11-2011, 06:32 AM

OK, here's the ultimate solution to your original problem as stated.

To calculate taxes based on income-after-tax without a circular reference do this:

Tax = income / (1 + tax rate)

For example:

A1 = net income before property taxes
A2 = property tax rate

property tax amount:
=A1/(1+A2)