• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vector product of n vectors

U

User9909

New Member
Hi Everyone,
Is there a way to get a cross product of an array of n elements in excel?
What I am after is something like this: I have two columns of data having 10 elements each (say A1 to A10 and B1 to B10).
I want is:
Ans= A1*B10+A2*B9+A3*B8+A4*B7+. . . .+A9*B2+A10*B1
Thank you for your time guys.
Stay awesome.
 
bosco_yip said:
Maybe,

=SUMPRODUCT((A1:A10)*TRANSPOSE(N(OFFSET(B1,{9,8,7,6,5,4,3,2,1,0},))))

Regards
Bosco
Click to expand...
Elegant... First time ever I see N () being used. Just checked out what it does. Now wondering why didn't I used it before myself.
Seeing all those smart applications of the versatile SUMPRODUCT (), I can't help but wondering why in Excel courses (in my country) so little attention is paid to this function, even in advanced trainings. Luckily we have :awesome:-blog/Forum to sharpen our tools.
 
Additionally, is there a way to change the arguments of the offset function so that the formula can be copied to multiple rows. I tried to use the Concatenate function in the attached file but was unable to do so (Cell F5). Thanks again for your help guys.
 

Attachments

User9909 said:
Additionally, is there a way to change the arguments of the offset function so that the formula can be copied to multiple rows. I tried to use the Concatenate function in the attached file but was unable to do so (Cell F5). Thanks again for your help guys.
Click to expand...
Try,

In G2, copied down :

=SUMPRODUCT(($A$2:A2)*N(OFFSET($B$2,ROW(A1)-ROW(A$1:A1),)))

Regards
Bosco
 

Attachments

You must log in or register to reply here.
Back
Top