Array discoveries

I thought I'd share a few discoveries.
I've just started to learn about Array formulas.

Sumproduct rule:
=SUMPRODUCT(--MID("123",{1,2,3},1),--ISNUMBER({1,2,3})) --> 6
=SUMPRODUCT(MID("123",{1,2,3},1),ISNUMBER({1,2,3})) --> 0
=SUM(MID("123",{1,2,3},1)*ISNUMBER({1,2,3})) --> 6
="1"*True --> 1

Numerical arrays treated as text:
=SUM(--ISERROR(FIND("2";ROW(2:5)))) --> 3
=SUM(--ISERROR(FIND("2";COLUMN(B:E)))) --> 3

Text array
=SUM(--ISERROR(FIND("B";CHAR(COLUMN(B:E)+64)))) --> 3

A good tool to see what Excel is doing:
Mark a piece in a formula at the Function bar and press F9. Esc or Enter

Ola

and Ola, if you have ver 2002 / 2003 - tools / formula auditing / evaluate
formula is also a great tool


"Ola" <[email protected]> wrote in message
news:[email protected]...
>I thought I'd share a few discoveries.
> I've just started to learn about Array formulas.
>
> Sumproduct rule:
> =SUMPRODUCT(--MID("123",{1,2,3},1),--ISNUMBER({1,2,3})) --> 6
> =SUMPRODUCT(MID("123",{1,2,3},1),ISNUMBER({1,2,3})) --> 0
> =SUM(MID("123",{1,2,3},1)*ISNUMBER({1,2,3})) --> 6
> ="1"*True --> 1
>
> Numerical arrays treated as text:
> =SUM(--ISERROR(FIND("2";ROW(2:5)))) --> 3
> =SUM(--ISERROR(FIND("2";COLUMN(B:E)))) --> 3
>
> Text array
> =SUM(--ISERROR(FIND("B";CHAR(COLUMN(B:E)+64)))) --> 3
>
> A good tool to see what Excel is doing:
> Mark a piece in a formula at the Function bar and press F9. Esc or Enter
>
> Ola
>

Hi Julie,
Good point. Because I think...
- The Insert Function: f(x)
- The Evaluate Formula: {f(x)}
- And the F9 evaluation.
....should all be integrated in One Dialogue box.

The are all good, integrated they would be great.
And integrated with Help + FAQ's + Discussion group online, that would be
something.

Ola