Want to avoid a circular ref with an "if"

[Naomi]

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!

 

[Eduardo]

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

 

[albertmb]

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

 

[Gord Dibben]

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

 

[zvkmpw]

I want a cell to display a word if its contents is "0", ...

One way is to use
Format > Cells > Number
and under "Category" choose "Custom"
and under "Type" put
0;-0;"a word"

Replace "a word" with the actual word you want.

This approach doesn't use an "IF" formula, but it's more useful IMO.
The value is still numeric zero, so it can be used in arithmetic
formulas.

(I have Excel 2003.)

 

[Gord Dibben]

Very good.........I'll store this one away.


Gord