Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recognizing Unstable Values in Excel
I apologize if someone already saw or answered this question ... I can't find
which subcategory of Excel groups where it apparently was posted earlier. My question is how can you write an if then statement that recognizes that the cell has an unstable answer. When I use the statement, IF(A1="#DIV/0!",1), the cell doesn't return the value of "1" when the values are unstable. How would I do this? -- Ken |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recognizing Unstable Values in Excel
Try,
=ISERROR(A1)*1 Mike "Ken" wrote: I apologize if someone already saw or answered this question ... I can't find which subcategory of Excel groups where it apparently was posted earlier. My question is how can you write an if then statement that recognizes that the cell has an unstable answer. When I use the statement, IF(A1="#DIV/0!",1), the cell doesn't return the value of "1" when the values are unstable. How would I do this? -- Ken |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recognizing Unstable Values in Excel
=IF(ISNUMBER(A1),"Stable","Unstable")
So if A1 contains a formula that returns #DIV/0! or any other error, the formula above will display Unstable -- Gary''s Student - gsnu200812 "Ken" wrote: I apologize if someone already saw or answered this question ... I can't find which subcategory of Excel groups where it apparently was posted earlier. My question is how can you write an if then statement that recognizes that the cell has an unstable answer. When I use the statement, IF(A1="#DIV/0!",1), the cell doesn't return the value of "1" when the values are unstable. How would I do this? -- Ken |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recognizing Unstable Values in Excel
=--(ERROR.TYPE(A1)=2)
"Ken" wrote: I apologize if someone already saw or answered this question ... I can't find which subcategory of Excel groups where it apparently was posted earlier. My question is how can you write an if then statement that recognizes that the cell has an unstable answer. When I use the statement, IF(A1="#DIV/0!",1), the cell doesn't return the value of "1" when the values are unstable. How would I do this? -- Ken |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recognizing Unstable Values in Excel
On Sat, 8 Nov 2008 05:24:00 -0800, Ken wrote:
I apologize if someone already saw or answered this question ... I can't find which subcategory of Excel groups where it apparently was posted earlier. My question is how can you write an if then statement that recognizes that the cell has an unstable answer. When I use the statement, IF(A1="#DIV/0!",1), the cell doesn't return the value of "1" when the values are unstable. How would I do this? I don't know what you mean by "unstable", but one way to test specifically for a DIV/0 error in A1 is: =if(ERROR.TYPE(A1)=2,1) --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recognizing Unstable Values in Excel
You may want to check for an error first--to avoid having an error returned:
=IF(ISERROR(A1),IF(ERROR.TYPE(A1)=2,1,"not Div/0"),"not error") Ron Rosenfeld wrote: On Sat, 8 Nov 2008 05:24:00 -0800, Ken wrote: I apologize if someone already saw or answered this question ... I can't find which subcategory of Excel groups where it apparently was posted earlier. My question is how can you write an if then statement that recognizes that the cell has an unstable answer. When I use the statement, IF(A1="#DIV/0!",1), the cell doesn't return the value of "1" when the values are unstable. How would I do this? I don't know what you mean by "unstable", but one way to test specifically for a DIV/0 error in A1 is: =if(ERROR.TYPE(A1)=2,1) --ron -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recognizing Unstable Values in Excel
Hi,
Excel contains 7 built-in error types of which DIV/0 is one, the others are Error Type# #NULL! 1 #DIV/0! 2 #VALUE! 3 #REF! 4 #NAME? 5 #NUM! 6 #N/A 7 You can trap these with the following functions: =ISERR(A1) =ISNA(A1) =ISERROR(A1) =ERROR.TYPE(A1) You usually combine on of the above with an IF statement such as =IF(ISERR(A1),"",A1) However, it is common when you get a DIV/0 message to consider what is causing that and trapping that rather than the error itself, for example If A1=0 the formula =B1/A1 would return the DIV/0 error, so we would trap the value of A1 as follows: =IF(A1=0,"",B1/A1) In this case if A1 is blank or 0, the formula will show nothing in the cells. -- Thanks, Shane Devenshire "Ken" wrote: I apologize if someone already saw or answered this question ... I can't find which subcategory of Excel groups where it apparently was posted earlier. My question is how can you write an if then statement that recognizes that the cell has an unstable answer. When I use the statement, IF(A1="#DIV/0!",1), the cell doesn't return the value of "1" when the values are unstable. How would I do this? -- Ken |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table not recognizing same field values | Excel Discussion (Misc queries) | |||
Excel 2002: Unstable colour settings | Excel Discussion (Misc queries) | |||
Recognizing Real Values | Excel Discussion (Misc queries) | |||
Excel not recognizing equal values | Excel Worksheet Functions | |||
Excel 97 unstable | Setting up and Configuration of Excel |