ISERROR query
Posted by Chris D on January 09, 2002 12:31 PM
Hi all,
is there a listing of the errors that ISERROR covers ?
I'm a bit nervous about using "=IF(ISERROR....=TRUE" globally to eliminate any errors, where the type of error might mean something
(ie #DIV/0 or #REF or #N/A)
many thanks
Chris
Posted by Mark W. on January 09, 2002 12:39 PM
ANY Error (nt)
Posted by Mark W. on January 09, 2002 12:40 PM
You may want to take a look at the ERROR.TYPE worksheet function (nt)
Posted by Scott on January 09, 2002 12:44 PM
It will return true on #N/A, #REF!, #NULL, #VALUE!, #NAME?, #DIV/0, and #NUM!
IMO, it's always best to use isna, isref etc. so that you know it's only identifying the errors that you want.
Posted by Aladin Akyurek on January 09, 2002 12:48 PM
nervous about using a catch-all thing as ISERROR. I think there is almost no situation it's appropriate to use, unless you're in debugging mode and/or you have no control over the computations that deliver data to your own model.
An example:
I see people (also at an advanced level) using
=IF(ISERROR(func(....
where func is SEARCH,FIND, or MATCH.
This functions are designed to return a number, so
=IF(ISNUMBER(func(...
would be sufficient to "suppress" #N/A when func does not succeed.
Aladin
=====
Posted by Mark W. on January 09, 2002 1:11 PM
Aladin makes a good point...
Furthermore, I believe (especially in the case
of lookup formulas) that the formula that uses
the results of the lookup should perform the
error handling -- not the the lookup function
itself. For example, if VLOOKUP can and does
return #N/A then leave it! If this error
adversely affects some arithmetic function then
let that function handle the error condition...
=SUMIF(range,"<>#N/A")
{=AVERAGE(IF(ISNA(range),"",range))}
{=AVERAGE(IF(ISNA(range),0,range))}
etc., etc., etc.
Posted by Chris D on January 09, 2002 1:35 PM
Thanks guys :-) advice much appreciated (NT)
Posted by Aladin Akyurek on January 09, 2002 3:17 PM
See for a justified use of ISERROR...
Aladin
Posted by Dwight on March 04, 2002 2:24 PM
Re: You may want to take a look at the ERROR.TYPE worksheet function (nt)
This does not work for some reason?
=IF(iserror(VLOOKUP(A9,[Feb02data.xls]report!$B$1:$D$30,3),0,VLOOKUP(A9,[Feb02data.xls]report!$B$1:$D30,FALSE))
