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