Microsoft KB Archive/72141

{|
 * width="100%"|

Function Macro to Calculate the Cross Product in Excel

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.1, 3.0, 4.0, 5.0
 * Microsoft Excel for the Macintosh, version 2.2, 3.0, 4.0, 5.0

-

SUMMARY
This article explains how to create a function macro that calculates the cross product of two arrays, or vectors. The function macro in this article takes two arrays (three rows by one column) of numbers, each representing a vector, and returns an array of the same dimensions representing the cross product of the two vectors.

MORE INFORMATION
The cross product, c = a x b, of the vectors a and b is a vector that is perpendicular to the plane of a and b. It can be illustrated by the following table:

           i   j   k            ========= c = a x b = m  n   o  = [(n*z)-(o*y)]i - [(o*x)-(m*z)]j + [(m*y)-(n*x)]k x  y   z

For example, given two vectors a and b:

a = (1,2,3)

b = (4,5,6)

Vector c can be computed:

c = [(2*6)-(3*5)]i - [(3*4)-(1*6)]j + [(1*5)-(2*4)]k

= [12-15]i - [12-6]j + [5-8]k

= [-3]i - [6]j + [-3]k

= (-3,6,-3)

In Microsoft Excel, you can create a function macro to perform these calculations and return the results into an array. To do this, enter the following macro into a macro sheet:

  A1: Cross_Product A2: =RESULT(64) A3: =ARGUMENT(&quot;Vec1&quot;,64) A4: =ARGUMENT(&quot;Vec2&quot;,64) A5: =INDEX(Vec1,2,0)*INDEX(Vec2,3,0)-INDEX(Vec1,3,0)*INDEX(Vec2,2,0) A6: =INDEX(Vec1,3,0)*INDEX(Vec2,1,0)-INDEX(Vec1,1,0)*INDEX(Vec2,3,0) A7: =INDEX(Vec1,1,0)*INDEX(Vec2,2,0)-INDEX(Vec1,2,0)*INDEX(Vec2,1,0) A8: =RETURN(A5:A7)

EXPLANATION OF MACRO
  A1: Name of macro A2: Specifies that the macro will return an array. A3: The array specifying the first vector. A4: The array specifying the second vector. A5: Calculate the first array value. A6: Calculate the second array value. A7: Calculate the third array value. A8: Return the values in A5:A7.

The next step is to define the macro as a function macro by performing the following steps:


 * 1) Select cell A1 on the macro sheet.
 * 2) In Excel 3.0 or 4.0, select Formula/Define Name. In Excel 5.0, select Insert/Name/Define.
 * 3) Choose the Function option at the bottom of the Define Name dialog box and press the OK button.

Be sure to select three vertical cells before typing the function, and enter the function as an array formula by pressing CTRL+SHIFT+ENTER. For example, given the ranges A1:A3 (vector a) and B1:B3 (vector b), calculate the cross product by highlighting C1:C3, type the following function, and press CTRL+SHIFT+ENTER:

  =Cross_Product(A1:A3,B1:B3)

The results are shown below:

  A1: 1     B1: 4     C1: -3 A2: 2    B2: 5     C2:  6 A3: 3    B3: 6     C3: -3