Iserror for an array
 

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.

Iserror for an array
 

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

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

--
Biff
Microsoft Excel MVP


"diepvic" wrote in message
...
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.

Iserror for an array
 

Try the below

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

If this post helps click Yes
---------------
Jacob Skaria


"diepvic" wrote:

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.

Iserror for an array
 

Oops..correction..

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

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Try the below

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

If this post helps click Yes
---------------
Jacob Skaria


"diepvic" wrote:

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.

Iserror for an array
 

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

Iserror for an array
 

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

--
Biff
Microsoft Excel MVP


"diepvic" wrote in message
...
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

Iserror for an array
 

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
---------------
Jacob Skaria


"diepvic" wrote:

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

Iserror for an array
 

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.



--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"diepvic" wrote:

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

Iserror for an array
 

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