Function Macro to Calculate the Cross Product in Excel

 Last reviewed: November 4, 1994
Article ID: Q72141
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.1, 3.0, 4.0, 5.0
  • Microsoft Excel for Macintosh, versions 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("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

KBCategory: kbprg kbmacro
KBSubcategory:

Additional reference words: 3.0 3.00 4.0 4.00 5.0 5.00 Vector Plane

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 4, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.