Array Formula and ISNUMBER()for a list?

=(SUMPRODUCT(ISNUMBER(A1:A5)+0)=ROWS(A1:A5))+0

1 ==> All numbers

0 ==> Not all numbers

I have column that could be either numbers, blank, or dash.

If the column is not all numbers I want to do one think if not I want to do another.

I though I could handle this with an array formula, but the isnumber() evaluation against a single item of "true" does not seem to work.

{=IF( (ISNUMBER(a1:a5)="TRUE"),"number", "numbers and letters")}
CTRL-SHIFT-ENTER

What am I doing wrong?


I also wanted to do the same thing with conditional formatting (and change the cell color), but I could not figure out how to do =ISNUMBER(a1:a5) as an array formula with conditional formatting.

Any thoughts on this also?


Thanks for your help.

Alan

Click to expand...

=If(Count(A1:A5)=Rows(A1:A5),"number","number and letters")

Aladin & NBVC,

Thanks for the replies... I like to see the various options... The more different ways I can learn about a solution the better i am the next time.

It now makes sense about comparing an array to an array instead of an array to 'TRUE'.

NBVC, It took me second to understand the count() option, but as count only counts numbers I see how that works.


Aladin, your tight compact answers always interest me.

I am curious as to why in
=(SUMPRODUCT(ISNUMBER(A1:A5)+0)=ROWS(A1:A5))+0

You used +0 Twice. It looks like you used it once to coerce the isnumber() result and once to coerce the final sumproduct(). I assumed your final sum product would already be a number and not need to be coerced to be a number.

Also why are you using +0 in this case as opposed to --

I have been following some of your work for a while and have kept this link
http://www.mrexcel.com/board2/viewtopic.php?p=345161#345161
as a reference. The way I and other have read and referenced it in the past was that if one is going to coerce the sumproduct() use -- not +0 or even *1

I have used variations of your sumproduct() answers to replace large/many array formulas in the past to improve over all speed calculations. Unfortunately the logic is always harder for me to grasp then an array formula and an if() statement. Any tips on getting one's mind to think though sumproduct() instead of array formulas?


Out of curiosity is there a logical easy way to do my original formula {=IF( (ISNUMBER(a1:a5)="TRUE"),"number", "numbers and letters")}
but compare it to some sort of an array and get correct results?


thanks again

Alan

...

Aladin, your tight compact answers always interest me.

Click to expand...

Thanks. It's better/more efficient for the current task to invoke Count instead of SumProduct though.

I am curious as to why in
=(SUMPRODUCT(ISNUMBER(A1:A5)+0)=ROWS(A1:A5))+0

You used +0 Twice. It looks like you used it once to coerce the isnumber() result and once to coerce the final sumproduct(). I assumed your final sum product would already be a number and not need to be coerced to be a number.

Click to expand...

The first +0 coerces the result array IsNumber return into a numeric array of 1/0's. The second +0 coerces the result of the equality test:

SUMPRODUCT(...)=ROWS(...)

If you remove the last/outer +0, the final evaluation would be either TRUE or FALSE. Coercing this to 1/0 is easier to process for it's a number and in addietion, you can custom format the formula cell as:

[=0]"Not All Numbers";[=1]"All Numbers"

Also why are you using +0 in this case as opposed to --

I have been following some of your work for a while and have kept this link
http://www.mrexcel.com/board2/viewtopic.php?p=345161#345161
as a reference. The way I and other have read and referenced it in the past was that if one is going to coerce the sumproduct() use -- not +0 or even *1

Click to expand...

The temporal profiling I've done shows -- and +0 as (almost) equal. I think I reported this observation in the link you quote. As to why +0 here? I tend to use +0 when there is a single array to process. Also, when a text date must be coerced. Thus:

>="1-Jan-04"+0

instead of

>=--"1-Jan-04"

I have used variations of your sumproduct() answers to replace large/many array formulas in the past to improve over all speed calculations. Unfortunately the logic is always harder for me to grasp then an array formula and an if() statement. Any tips on getting one's mind to think though sumproduct() instead of array formulas?

Click to expand...

Actually, both types of formulas follow the same logic:

[1]

{=SUM(IF(A2:A10="X",IF(B2:B10="Y",1,0),0))}

[2]

{=SUM((A2:A10="X")*(B2:B10="Y"))}

[3a]

=SUMPRODUCT((A2:A10="X")*(B2:B10="Y"))

[3b] Reverting back to the comma syntax with explicit coercion added:

=SUMPRODUCT(--(A2:A10="X"),--(B2:B10="Y"))

As you see from [1] and [2], you need to rewrite IF in a binary form:

IF(A2:A10="X",IF(B2:B10="Y",1,0),0)

==>

(A2:A10="X")*(B2:B10="Y")

==> (when fed to SumProduct)

--(A2:A10="X"),--(B2:B10="Y")

Whenever you can take this step, you can construct an equivalent SumProduct formula.

Out of curiosity is there a logical easy way to do my original formula {=IF( (ISNUMBER(a1:a5)="TRUE"),"number", "numbers and letters")}
but compare it to some sort of an array and get correct results?

Click to expand...

{=IF(AND(ISNUMBER(A1:A5)),"number", "numbers and letters")}