Array discoveries

[Ola]

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

 

[JulieD]

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

 

[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