Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
how can I avoid "server busy" message during ole automation | Excel Discussion (Misc queries) | |||
Avoid "update Links" promt in e-mailed pivot tables? | Excel Discussion (Misc queries) | |||
How do I avoid "Missing PDFMaker files" error? | Excel Discussion (Misc queries) | |||
Text formatted as "General" in Excel 2002. How do I avoid that? | Excel Discussion (Misc queries) |