Another SUMPRODUCT array anomaly

[Jerry W. Lewis]

In an otherwise empty worksheet, enter 1 in G1 and I1 and a number (<>0)
in I5. Now, in each of C1:C2 enter the formula
=SUMPRODUCT((I1V1=1)*TRANSPOSE(G1:G118=G2),I5V5)

Why does the result of this formula depend upon the cell that it is
entered in?

How is the formula in C2 returning the value in I5 even though
(I1V1=1)*TRANSPOSE(G1:G118=G2) should be an array of zeros?

Jerry

 

[Peo Sjoblom]

Fid you array enter it? TRANSPOSE even inside SUMPRODUCT always need to be
array entered

 

[Jay Petrulis]

Hi Jerry,

Using Excel 2003.

This is really weird. If I copy the formula to C3 and C4, I have your
identical formula in four cells.

With 1 in G1 and I1, 100 in I5, and all other cells blank (except
c1:c4, of course)...

If G2 is blank or 0, I get:
C1: 0
C2: 100
C3: 100
C4: 100

If G2 is 1, I get:
C1: 100
C2: 100
C3: 0
C4: 0

If G2 is 2 or text, I get:
C1: 0
C2: 100
C3: 0
C4: 0

Ugh!!!

Regards,
Jay Petrulis

 

[Jay Petrulis]

Hi Peo,

Nice catch. Array entry does return the correct and expected results.
However, I do not understand why the array entry is required, in THIS
instance.

The item of concern is the first entry in the corresponding arrays.
Thus, the transpose non-array entered should still calculate the first
set, I would think.

Other items in the arrays might be wrong without the array entry for
TRANSPOSE, but not the first.

Obviously, I could be off my rocker here.

Regards,
Jay Petrulis

 

[Jerry W. Lewis]

No, and array entry does fix the problem. I second Jay's kudos about a
good catch!

Any idea about what it is doing without array entry that the result
depends on where the formula resides?

Also, has anyone compiled a list of functions that require array entry
inside SUMPRODUCT?


Jerry

 

[Jerry W. Lewis]

And possibly the more important follow-on question, can anyone think of
a SUMPRODUCT formula where there is a different result with array entry
and the array entered result is the less desirable result?

Jerry

 

[Harlan Grove]

Jerry W. Lewis wrote...
....

Any idea about what it is doing without array entry that the result
depends on where the formula resides?

Implicit array indexing.

Enter {1;2;3;4;5;6;7;8} in A2:A9, {1,10,100,1000} in B1:E1, select
B2:E9, type the formula =$A2:$A9*B$1:E$1 and press [Ctrl]+[Enter]
rather than [Ctrl]+[Shift]+[Enter]. You should get the same result as
if you had entered B2:E9 as an array formula. Clear B2:E9, select
C4:F11, type the same formula and press [Ctrl]+[Enter].

This has been in Excel for a LONG, LONG time, but as with too much else
has never been properly documented.

Also, has anyone compiled a list of functions that require array entry

inside SUMPRODUCT?

It's not just inside SUMPRODUCT.

And we're talking phylum, class, order, family, genus and species when
it comes to a full description of built-in function array semantics.
There are three phyla: functions that always return arrays (FREQUENCY),
functions that always return scalars (SUM) and functions that can
return both (IF).

Within the first phylum, there are two classes. Those that provide
implicit array indexing, and those that don't. Given the setup above in
A2:A9 and B1:E1, select C3:E5, type the formula

=TRANSPOSE($A$2:$A$9)

and press [Ctrl]+[Enter]. Now select C7:E9, type the formula

=MMULT($C$3:$E$5,$C$3:$E$5)

and press [Ctrl]+[Enter]. Then again, select C3:C5 again, change the
formula to

=TRANSPOSE({1;2;3;4;5;6;7;8})

and press [Ctrl]+[Enter]. Then change the formula to

=TRANSPOSE($A$2:$A$9*10)

So it seems implicit array indexing should be called implicit range
indexing.

Anyway, if you want to learn how Excel works, read the developer
documentation and source code for Gnumeric.