Want to avoid a circular ref with an "if"
 

I want a cell to display a word if its contents is "0", but whenever i apply
an "if" statement to that cell, it reads itself and returns a circular ref!
How can I apply a formula to the same cell it rests in, and then use that
same formula going down a range? I think the answer will smack me in the
head when I see it posted.
thanks!

Want to avoid a circular ref with an "if"
 

Hi,
sorry but it not make sense and example will clarify, thanks

"Naomi" wrote:

I want a cell to display a word if its contents is "0", but whenever i apply
an "if" statement to that cell, it reads itself and returns a circular ref!
How can I apply a formula to the same cell it rests in, and then use that
same formula going down a range? I think the answer will smack me in the
head when I see it posted.
thanks!

Want to avoid a circular ref with an "if"
 

Hi Naomi hope this will answer your question:

Go to 'Tools', then select 'Options', then select 'Calculation',
Then tic 'Iteration' and write a number of maximum iterations you would
like. This will allow a cell to calculate itself.

regards
Albert

"Naomi" wrote:

I want a cell to display a word if its contents is "0", but whenever i apply
an "if" statement to that cell, it reads itself and returns a circular ref!
How can I apply a formula to the same cell it rests in, and then use that
same formula going down a range? I think the answer will smack me in the
head when I see it posted.
thanks!

Want to avoid a circular ref with an "if"
 

Just a point here for Naomi

How would a cell with a formula referring to itself ever contain a zero?

Maybe =A1=0 if you enable Iterations and set to 1

But then how would you evaluate A1 being zero using a formula in A1?

If you enter =IF(A1=0,"word") it will evaluate to FALSE

If you enter =IF(A1=0,"word","not 0") it will always return "not 0"

Better you should re-think what you want done.

Excel has many cells............use them.

Enter your formula in A1 to refer to another cell.

=IF(B1=0,"word","not zero")

Just remember that if B1 is blank Excel will evaluate as zero so you may
want to trap for B1 being blank.

=IF(AND(ISNUMBER(B1),B1=0),"word","not zero")


Gord Dibben MS Excel MVP


On Mon, 23 Nov 2009 12:05:02 -0800, albertmb
wrote:

Hi Naomi hope this will answer your question:

Go to 'Tools', then select 'Options', then select 'Calculation',
Then tic 'Iteration' and write a number of maximum iterations you would
like. This will allow a cell to calculate itself.

regards
Albert

"Naomi" wrote:

I want a cell to display a word if its contents is "0", but whenever i apply
an "if" statement to that cell, it reads itself and returns a circular ref!
How can I apply a formula to the same cell it rests in, and then use that
same formula going down a range? I think the answer will smack me in the
head when I see it posted.
thanks!