Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Iserror for an array
Wow,
thanks so much , Shane Your answer does clear my thought |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
iserror help | Excel Worksheet Functions | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Iserror & VBA | Excel Discussion (Misc queries) | |||
ISERROR - #NAME? | Excel Worksheet Functions | |||
iserror | Excel Discussion (Misc queries) |