Circular Reference 0 value cells

Question

Greetings all,

Here is my situation.

I have a spreadsheet that calculates regional values and some of the values are $0. From that calculation I want to calculate regional value/statewide=% value to get the percentage.

Well if a value of a cell is $0, I get the circular reference warning. I tried to create an If funtion to represent %,if0,0,regional value/statewide value.  In essence I want to return a $0 value instead of the #div/0! error.

Your help is much appreciated. Now I am going to take asprin for this headach.

Answer 1

Show us your formula.  The fact that a cell has a 0 does not cause a circular reference.  There is something wrong with your formula(s).

Here is a circular reference

A1:  =B1

B1:  =A1

---

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

Answer 2

mylewis wrote:

I have a spreadsheet that calculates regional values and some of the values are $0. From that calculation I want to calculate regional value/statewide=% value to get the percentage.

[....] I tried to create an If funtion to represent %,if0,0,regional value/statewide value.  In essence I want to return a $0 value instead of the #div/0! error.

If A1 is the regional value and B1 is the statewide value, try the following in C1 (e.g.):

=IF(B1=0, 0, A1/B1)

formatted as Percentage.  Note that there is no need to test for A1=0; the formula takes care of that situation automagically.

However, that displays 0%, not $0, when either the regional or statewide value is zero.  I don't think you really meant $0 per se.  It would make no sense for a cell to represent percentage or dollars depending on the values.  Well, it would be poor design, anyway.

If that does not address you problem, I think you will need to provide more details -- cell names, cell values, and formulas.