Function Macro to Calculate the Cross Product in Excel

ID: Q72141

The information in this article applies to:

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("Vec1",64)
   A4: =ARGUMENT("Vec2",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


REFERENCES

Microsoft Excel Help, version 5.0
"Function Reference," version 4.0, pages 24-25, 238
"Function Reference," version 3.0, pages 15, 128-129

Additional query words: 3.0 4.0 5.0 Vector Plane 


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: March 21, 1999