Consulting

Results 1 to 7 of 7

Thread: Sleeper: General SUMPRODUCT/ARRAY Questions

  1. #1

    Sleeper: General SUMPRODUCT/ARRAY Questions

    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?
    Last edited by Saladsamurai; 09-24-2009 at 12:23 PM.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by Saladsamurai
    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.

    Quote Originally Posted by Saladsamurai
    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.

    Quote Originally Posted by Saladsamurai
    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.

    Quote Originally Posted by Saladsamurai
    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?

    Quote Originally Posted by Saladsamurai
    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]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    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?

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    Quote Originally Posted by xld
    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!

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by Saladsamurai
    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    Quote Originally Posted by xld
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •