Cross Products with Excel and VBA

Microsoft Excel has no built-in spreadsheet functions to calculate the cross product of two vectors. For this reason, certain methods are needed to perform the calculations – either by setting up a custom formula in the spreadsheet or by creating a custom VBA function. This article will guide you through these two techniques.

Cross-mathematical recording – Cross products with 3D Vector components

Let’s start with a short presentation of the basic principles of the mathematical workings of multiplying two vectors in three-dimensional space.

We have two vectors a and b, where i, j, k are standard base vectors (a1, a2 and a3 are the vector components a, and b1, b2, b3 are the vector components b).

alt=vector a and vector b data-orig-width=404 data-orig-height=94 data-ezsrc=http://31.220.61.170/wp-content/uploads/2020/10/Cross-Products-with-Excel-and-VBA.jpg data-ez= />

(Note: Some people may be more accustomed to reading the axes, i.e. the plasma, that represent the components of the vector along the xyz axis. In this article we will end the 1-2-3 chord, because it will facilitate the work in Excel).

The product of vectors a and b can be represented in matrix form, as shown below (also referred to as format-defining) :

alt= vector product a and b vector data width=401 vector data height=165 data-ezsrc=http://31.220.61.170/wp-content/uploads/2020/10/1604151376_669_Cross-Products-with-Excel-and-VBA.jpg data-ez= />

alt=Plug in! data-ezsrc=/utilcave_com/social/pin_it.png />

alt=Change to Facebook data-ezsrc=/utilcave_com/social/fb_share.png />

In addition, the matrix can be expanded using the Sarrus rule:

alt=Matrix extended data-orig-width=728 data-orig-height=120 data-ezsrc=http://31.220.61.170/wp-content/uploads/2020/10/1604151377_700_Cross-Products-with-Excel-and-VBA.jpg data-ez= />

And finally, we continue to expand it by expanding the cofactor:

alt=Matrix extended with cofactor exapnsion data-orig-width=883 data-orig-height=64 data-ezsrc=http://31.220.61.170/wp-content/uploads/2020/10/1604151378_771_Cross-Products-with-Excel-and-VBA.jpg data-ez= />.

Here ((a2b3 to a3b2) , (a3b1 to a1b3) , (a1b2 to a2b1)) is actually the endpoint of the cross-working vector.

To remember this sequence to calculate the 3 components of such an endpoint, one can use the mnemonics of xyzzy or 12332, i.e.

1. The first group (x) equals 23 min 32.
2. The second group (y) adds 1 to each number: 31 minus 13.
3. The third group (z) adds 1 to each number in the second group: 12 minus 21.

We will use this abbreviation in the development of our custom VBA functionality in the next section of this article.

Another case of transverse work is that of the scales (magnitudes) of the vectors a and b and of the sine of the angle between a and b.

alt=scales and vectors for interproduct original width=336 data-original height=54 data-ezsrc=http://31.220.61.170/wp-content/uploads/2020/10/1604151379_449_Cross-Products-with-Excel-and-VBA.jpg data-ez= />

As the calculation is very simple, it is not discussed in this article. We will focus on the calculation with vector components.

Download examples

First download the workbook with the examples in this article.
(Activate macros when using the file)

Vector transition in Excel

We can modify our worksheet to calculate the cross product using Excel formulas. Since Excel does not have built-in functions for cross product calculations, we need to carefully configure the calculations on the spreadsheet.

Excel configuration for scenario 1 – with3D vector components

alt=excel parameter vector data-orig-width=883 data-orig-height=64 data-ezsrc=http://31.220.61.170/wp-content/uploads/2020/10/1604151380_498_Cross-Products-with-Excel-and-VBA.jpg data-ez= />

The figure below shows the Excel formula in cells C7, D7 and E7 :

alt=Select 1 Excel Setup data-orig-width=889 data-orig-height=207 data-ezsrc=http://31.220.61.170/wp-content/uploads/2020/10/1604151382_640_Cross-Products-with-Excel-and-VBA.jpg data-ez= />

alt=Plug in! data-ezsrc=/utilcave_com/social/pin_it.png />

alt=Change to Facebook data-ezsrc=/utilcave_com/social/fb_share.png />

The result is as follows:

alt=Cross-referencing results to Ecel-Original data width=563 Data-Original height=203 data-ezsrc=http://31.220.61.170/wp-content/uploads/2020/10/1604151383_735_Cross-Products-with-Excel-and-VBA.jpg data-ez= />

alt=Plug in! data-ezsrc=/utilcave_com/social/pin_it.png />

alt=Change to Facebook data-ezsrc=/utilcave_com/social/fb_share.png />

So we have the answer: a x b = -3i – 6j -3k.

Cross product with VBA

To avoid having to configure the formula in Excel in a complicated way every time you need to calculate a cross product, you can create your own custom VBA function.

The CrossProduct macro below uses the 12332 (or xyzzy) mnemonic technique with the For-Next loop to calculate three vector components of the CrossProduct.

Table a of the function stores the mnemonics in 3 groups of 4 elements: 2332, 3113 and 1221, which are used for construction:

1. a2b3 – a3b2
2. a3b1 – a1b3
3. a1b2 – a2b1

Function CrossProduct(v1 as variant, v2 as variant) as variant
Dim i as a whole
Dim x as a whole
Dim a
Dim answer(1-3) ‘define an array with 3 slots from 1 to 3
‘this array contains a series of vector components
‘with 3 groups of 4 index numbers
a = Array(2), 3, 3, 2, 3, 1, 1, 1, 3, 1, 2, 2, 1)
For i = 1 – 3
x = (i – 1) * 4
Reply(i) = _
v1(a(0 + x)) * v2(a(1 + x)) – v1(a(2 + x)) * v2(a(3 + x))
Next
Cross product = answer
Final function

Since the function returns an array (no value at all), we must use it as an array function when we apply it to a sheet. See figure below, C7:E7 range.

• First select the range C7:E7
• Type = Cross product (C4:E4,C5,E5)
• Complete the process by pressing CTRL + SHIFT + ENTER.

alt=VBA cross product function used as input in Excel is-orig-width=563 data-orig-height=264 data-ezsrc=http://31.220.61.170/wp-content/uploads/2020/10/1604151385_674_Cross-Products-with-Excel-and-VBA.jpg data-ez= />

alt=Plug in! data-ezsrc=/utilcave_com/social/pin_it.png />

alt=Change to Facebook data-ezsrc=/utilcave_com/social/fb_share.png />

(Note: The bar formula indicates a table formula in square brackets {}. Do not print it yourself. They will disappear if you change the formula)

The macro can be expanded to allow a horizontal or vertical reaction.

An optional orientation argument with a default value of 1 is added at the bottom of the advanced VBA function. A query has also been added to select the case:

• When alignment = 1 horizontal response feedback
•  If the orientation = 2, we return the answer in vertical orientation.

CrossProduct(v1 as variant, v2 as variant, additional orientation as a whole = 1) as VariantDim i as IntegerDim x as IntegerDim aDim response(1 to 3) define a 3-box array from 1 to 3 this array contains a sequence of vector components with 3 groups of 4 index numbers = Array(2) this array contains a sequence of vector components with 3 groups of 4 index numbers = Array(2) this array contains a sequence of vector components with 3 groups of 4 index numbers = Array(2), 3, 3, 2, 3, 1, 1, 3, 1, 2, 2, 1)For i = 1-3x = (i – 1) * 4 Answer(i) = _v1(a(0 + x)) * v2(a(1 + x)) – v1(a(2 + x)) * v2(a(3 + x))Next’ to check the alignment of the outputSelect the alignment of the frameSelect 1 ‘default, horizontalSelectProst = answerSelect 2 ‘verticalSelectProst = application.transpose(answer) End Select the end of the function

We can now use the VBA function to return the answer vertically. The screenshot below shows that the value 2 is assigned to the last argument of the function.

alt=Configuration of the transverse direction of the products via the user function vba data-orig-width=484 data-orig-height=210 data-ezsrc=http://31.220.61.170/wp-content/uploads/2020/10/1604151386_95_Cross-Products-with-Excel-and-VBA.jpg data-ez= />

alt=Plug in! data-ezsrc=/utilcave_com/social/pin_it.png />

alt=Change to Facebook data-ezsrc=/utilcave_com/social/fb_share.png />

Conclusion

In this article we have discussed the calculation of the terrain product of two vectors. In fact, the problem also applies to other cases of matrix calculations, such as the adjacent matrix, the matrix of co-factors, the determinant, matrix multiplication, and so on.

Although Excel does not have built-in functions for working with this kind of math, you can use it to work with both custom formulas and VBA functions (as well as mass function input methods).

Related Tags:

excel cross product of two columns,dot product in excel,excel matrix example,excel for mac mmult,sumproduct matrix multiplication,cross product wiki,excel dot product,vector cross product,cross product calculator,angle between two vectors,excel vector dot,working with vectors in excel,matrix calculation in excel,c cross product,unity cross product,2d cross product,excel cross product function,crossproduct in excel,cross product determinant,cross product perpendicular,how to do cross product