ISERR vs ISERROR

[Biff]

Hi Folks,

The only difference I can see between these two is that
ISERR will not handle #N/A. Also, what about ISNA? Is
there any advantage in using ISERROR over the others?
None of the books I have, or XL Help for that matter,
really differentiates these functions but for the #N/A
error. Anyone have some insight on this?

Thanks
Biff

 

[Charles Williams]

Hi Biff,


ISERROR will trap all error values.
ISERR will trap all error values except #N/A
ISNA will only trap #N/A

so if you want to trap all errors use ISERROR.


hth
Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com

 

[Anon]

Hi Folks,

The only difference I can see between these two is that
ISERR will not handle #N/A. Also, what about ISNA? Is
there any advantage in using ISERROR over the others?
None of the books I have, or XL Help for that matter,
really differentiates these functions but for the #N/A
error. Anyone have some insight on this?

Thanks
Biff

I can't see why you are asking a question to which you are giving the
answer! The only difference between ISERROR and ISERR is exactly as you
state: ISERROR refers to all error values, whereas ISERR refers to all
excelp #N/A. (ISNA refers only to #N/A.) Which to use depends on what you
want to do. It's rather like COUNT and COUNTIF - use the former if you want
to count everything and the latter if you want less than everything.

 

[Andy Brown]

Perhaps Biff's question is "if ISERROR does all that ISERR can & more,
what's the point of ISERR?".

Y'know, kinda phispholosphical.

Rgds,
Andy

 

[Bernard Liengme]

The returned value #N/A is not always an error. In a lookup for example you
may expect to get NA sometimes. But you never 'expect' to get #VALUE! or
#DIV/0! - they are always errors. So the Excel programmers gave us a way of
telling the two cases apart for advanced applications. =ISERR(..) is more
convenient than =AND(ISERROR(..), NOT ISNA(...))
Bernard

 

[Harlan Grove]

The only difference I can see between these two is that
ISERR will not handle #N/A. Also, what about ISNA? Is
there any advantage in using ISERROR over the others?
None of the books I have, or XL Help for that matter,
really differentiates these functions but for the #N/A
error. Anyone have some insight on this?

The only difference is that #N/A isn't as much an error value as the other error
values are. At least that's what should be the case. However, since Lotus
fubarred @FIND in 123R2 (1986), returning ERR when the substring wasn't found in
the string searched, the distinction between NA and ERR (in 123), so #N/A and
the other error values (in Excel), has been obscured if not lost entirely. Also,
if Lotus had implemented NA correctly, it wouldn't propagate through most
formulas - it'd be treated more like missing values in stats packages. But Lotus
*did* treat NA like an error, and Microsoft copied 123 functionality in Excel
back in the days when Lotus controlled the lion's share of the application
software market. The rest, as they say, is history.

More 123 background. 123 contains @ISNA and @ISERR. @ISNA(@NA) returns 1 (True),
but @ISNA(@ERR) returns 0 (False). On the other hand, @ISERR(@NA) returns 0, but
@ISERR(@ERR) returns 1. So Excel's ISNA and ISERR functions are modelled on
123's corresponding functions, and date from the period when Microsoft believed
they had to copy as much 123 functionality as possible. ISERROR was an Excel
extension, and has become more practical than the other two functions. Well,
more practical than ISERR. There may still be sound arguments for using ISNA to
catch lookup values not found in MATCH or ?LOOKUP calls, which would allow, say,
#REF! and #NAME? results to propagate, which is more often than not a very good
thing.

To be as minimalist as possible, Excel doesn't need anything other than ISERROR
and ERROR.TYPE. ISERR and ISNA are superfluous. But they need to continue to
exist in Excel in order to support workbook that still depend on them.

 

[Biff]

Exactly!

After reading Harlan's and Bernard's responses, I can see
where the use of ISNA over ISERROR would be more practicle
in certain situations where #N/A could lead to errors
downstream.

Biff

 

[Biff]

Thanks to everyone for their input!

Biff

 

[Harlan Grove]

The returned value #N/A is not always an error. In a lookup for example you
may expect to get NA sometimes. But you never 'expect' to get #VALUE! or
#DIV/0! - they are always errors. . . .

=FIND("this","is a counterexample")

Unfortunately, #VALUE! isn't always an error like #REF! and #NAME? are. In some
instances, it represents the same thing that #N/A does in different contexts.
This is only Microsoft's fault to the extent that they believed they had to
duplicate 123 functionality as closely as possible. The actual blame for this
thoroughly boneheaded bit of semantics is exclusively Lotus Development Corp's.

. . . So the Excel programmers gave us a way of
telling the two cases apart for advanced applications. =ISERR(..) is more
convenient than =AND(ISERROR(..), NOT ISNA(...))

Actually, the Lotus programmers (though maybe credit goes to the VisiCalc
programmers) gave the world @ISNA and @ISERR, the functionality of which
Microsoft copied in Excel with ISNA and ISERR. Excel's ISERROR function is the
extension.