Sleeper: General SUMPRODUCT/ARRAY Questions

[Saladsamurai]

I know there are a lot of questions here; answer them at your convenience folks. And if I discover the answers, I will post back as well.

These questions will help me understand the logic a lot better. (And yes, I am continually reading xld's step-by-steps, but I am sometimes too dumb too even follow it )

Alright. I have searched and searched. The excel help file is silly.

All I want to do is confirm that when we use two Multidimensional Arrays in SUMPRODUCT the order of operations is indeed what I belive it is.

Question 1: Is the following correct?
By playing around with it, it seems that if I use two 2 x 2 Arrays as my arguments, then SUMPRODUCT(Array1, Array2) will do the following:

Multiply corresponding elements of each array, which would, in theory, yield another 2 x 2 Array. And then it SUMS all of the elements of the resultant Array.

For example:

Given two 2 x 2 Arrays
A = {a, b ; c , d}
B = {w, x ; y, z}

Then SUMPRODUCT(A , B) = (a*w) + (b*x) + (c*y) + (d*z)

I know it's a stupid question, but I keep getting confused with Matrix Multiplication which returns an Array (as opposed to a Scalar).

Question 2:
DO we ever use Ctl + Shift +Enter with SUMPRODUCT? [I don't think we do..but I am not positive]

Question 3:
What happens when we do not use Ctl + Shift + Enter with MMULT() ? [I am under the impression that it will just return ONE element of the resultant Array]

Question 4:
By ANDing (i.e. using '*') You can SUMPRODUCT two Arrays of unequal Row-length so long as they
have the same Column-Length....right?

For Example:

SUMPRODUCT((A1:A4)*(B1:C4))

is EQUAL TO

SUMPRODUCT(A1:A4, B1:B4) + SUMPRODUCT(A1:A4, C1:C4

correct?

Question 5:

Can all of the SUMPRODUCT 'tricks' be applied to VBA SUMPRODUCT?

[Bob Phillips]

Alright. I have searched and searched. The excel help file is silly.

It suffers the problem of many, most, help files in that it tells you how to use it in its most basic form, it does not even attempt to explain the different situations it can handle.

Also, MS are not even properly aware of the way that we use SP. I have spoken to technical help writers at MS who knew nothing of what that paper I pointed you at explains.

All I want to do is confirm that when we use two Multidimensional Arrays in SUMPRODUCT the order of operations is indeed what I belive it is.

Question 1: Is the following correct?
By playing around with it, it seems that if I use two 2 x 2 Arrays as my arguments, then SUMPRODUCT(Array1, Array2) will do the following:

Multiply corresponding elements of each array, which would, in theory, yield another 2 x 2 Array. And then it SUMS all of the elements of the resultant Array.

For example:

Given two 2 x 2 Arrays
A = {a, b ; c , d}
B = {w, x ; y, z}

Then SUMPRODUCT(A , B) = (a*w) + (b*x) + (c*y) + (d*z)

I know it's a stupid question, but I keep getting confused with Matrix Multiplication which returns an Array (as opposed to a Scalar).

Yes, that is correct.

Question 2:
DO we ever use Ctl + Shift +Enter with SUMPRODUCT? [I don't think we do..but I am not positive]

If there is an array operation within one of the conditions, it is necessary to C-S-E it, but in that case, you might just as well use SUM as SUMPRODUCT.

Question 3:
What happens when we do not use Ctl + Shift + Enter with MMULT() ? [I am under the impression that it will just return ONE element of the resultant Array]

MMULT in its essnce is not a C-S-E formula, it multiplies n columns by n rows and retunrs a scalar.

What is the issue you are referring to?

Question 4:
By ANDing (i.e. using '*') You can SUMPRODUCT two Arrays of unequal Row-length so long as they
have the same Column-Length....right?

For Example:

[vba]
SUMPRODUCT((A1:A4)*(B1:C4))
[/vba]

is EQUAL TO

[vba]SUMPRODUCT(A1:A4, B1:B4) + SUMPRODUCT(A1:A4, C1:C4)[/vba]

correct?

You are correct in your final analysis, but incorrect in how you got there. The * is not an AND here because you do not have conditions so you are not returning TRUE/FALSE arrays. * is pure multiplication here, but multiplying array elements.



Question 5:

Can all of the SUMPRODUCT 'tricks' be applied to VBA SUMPRODUCT?[/QUOTE]

[Saladsamurai]

MMULT in its essnce is not a C-S-E formula, it multiplies n columns by n rows and retunrs a scalar.

What is the issue you are referring to?

Maybe you use MMUULT() for a specific purpose (perhaps nested within another function) and somehow utilize a 'scalar' result.

But, by definition 1 Matrix Multiplying another matrix will virtually alway yield another matrix.

Moreover, give some matrix A and another matrix B, if the two are multiplicatively conformable, then their product is a matrix, let's call it C, whose elements are linear combinations of the rows of A with the columns of B.

In order for two matrices to be considered multiplicatively conformable the number of columns in the first matrix must = the number of rows in the second.

That is if A is a m x n matrix and B is a p x q matrix, then A and B are multiplicatively conformable iff n = p.

The resultant matrix C will have dimensions m x q.

So this last point is important when utilizing MMULT() You must pre-determine the resultant matrix's dimensions in order to execute MMULT properly.

Example below: The top-left matrix is dimensions: 3 x 2 and the top-right is 2 x 2

Therefore, in order to get the resultant (bottom) matrix, you must highlight a 3 x 2 range before entering MMULT(Array1, Array2) and then press C-S-E.





There are 3 wrong ways to use MMULT() and they all result in the same answer:

If you do not highlight the result matrix's range and press either 'Enter' or 'C-S-E' it will return ONLY the first element of the resultant array.

If you do highlight the result matrix's range BUT do not press 'C-S-E' the 1st element of the resultant will be returned.

You dig? Or am I just telling you stuff you already know?

P.S. Did you mean to answer Question 5 and it was cut off? Or is it still up in the air?

[Bob Phillips]

Q5. Yes I meant to say yes as far as I am aware, but SUMPRODUCT is an array function (even though it is not array entered) which means that you cannot use Application., Worksheetfunction., or even Application.Worksheetfunction., you have to build the SUMPRODUCT expression as a string and evaluate that.

[Saladsamurai]

Q5. Yes I meant to say yes as far as I am aware, but SUMPRODUCT is an array function (even though it is not array entered) which means that you cannot use Application., Worksheetfunction., or even Application.Worksheetfunction., you have to build the SUMPRODUCT expression as a string and evaluate that.

Interesting. My VBA skills are so poor that I don't think I follow 100%, but I will just need to mess around with it for a few to figure it out (or confuse myself).

On a side note: If anyone cares, if you use a MAC and you try to use SUMPRODUCT to compute a conditional count and you forget to use the double unary (or some other coercion technique) instead of giving the message "VALUE!" in the selected cell, it will return a value of "0"

That could cause some problems!

[Bob Phillips]

On a side note: If anyone cares, if you use a MAC and you try to use SUMPRODUCT to compute a conditional count and you forget to use the double unary (or some other coercion technique) instead of giving the message "VALUE!" in the selected cell, it will return a value of "0"

Yet another reason not to use a Mac

[Saladsamurai]

Yet another reason not to use a Mac

Unless you run Windows on it .... and all MS products....which brings us back to your point