Iserror for an array

[diepvic]

Hi,
can I use Iserror for an array?
like ISERROR(A2:A300)
I've tried this with a non-error array. The result is TRUE, which means
there's an error in the array. I'm quite confused.

Pls help.

 

[T. Valko]

What are you trying to do? Are you wanting to know if there are any errors
in the range?

=SUMPRODUCT(--(ISERROR(A2:A300)))

 

[Jacob Skaria]

Try the below

=1=SUMPRODUCT(--(ISERROR(A2:A300)))

If this post helps click Yes

 

[Jacob Skaria]

Oops..correction..

=0<>SUMPRODUCT(--(ISERROR(A2:A300)))

If this post helps click Yes

 

[diepvic]

Thanks Jacob & T.Valko,

It works so perfectly
Anw, could you pls explain for me how it works? What does "--" mean here?

Thanks a lot

 

[T. Valko]

The ISERROR function returns an array of TRUE or FALSE. The double unary
coerces these to numeric 1 and 0:

--TRUE = 1
--FALSE = 0

Then SUMPRODUCT adds them up for the result.

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

 

[Jacob Skaria]

Try
=SUMPRODUCT(--{FALSE,TRUE,TRUE,FALSE})

On SUMPROUCT
http://xldynamic.com/source/xld.SUMPRODUCT.html

On unary minus operator
http://www.mcgimpsey.com/excel/formulae/doubleneg.html

If this post helps click Yes

 

[Shane Devenshire]

Hi,

First, if you want to know if there is an error in a range you could use

=ISERROR(SUM(A2:A300))

which would return TRUE if there was any error in the range.

Second, if you would be happy just to know if there is an error you could use

=SUM(A2:A300)

Which will return the error if there is one otherwise it will return an error.

Third, if you want to count the number of only one kind of error such as
#DIV/0! then you could use

=COUNTIF(A2:A300,"#DIV/0!")

Finally, the formulas
=SUMPRODUCT(--ISERROR(A2:A300)) or a similar function
=SUMPRODUCT(--ISERR(A2:A300))
works like this: the ISERROR function returns a string of TRUE's and FALSE's
which indicate for each cell in the range whether it is an error. The first
minus (right one) causes Excel to convert TRUE to -1 and FALSE to 0, the
second minus converts the -1 to 1. Then the SUMPRODUCT sums all the 1's.
You could enter the array formula
=SUM(--ISERR(A2:A300)) but this requires array entry (pressing
Shift+Ctrl+Enter) so to avoid that it is common to use SUMPRODUCT.
SUMPRODUCT also calculates somewhat faster.

 

[diepvic]

Wow,
thanks so much , Shane
Your answer does clear my thought