CROSS Product Function for Excel

=CROSS(vector_1, vector_2)

Argument	Description	Example
vector_a	A vector in R³ space	{4;1;1}
vector_b	A vector in R³ space	{2;5;0}

Download the Template

In the template file, navigate to the Matrices worksheet to see the CROSS function in action.

Description

The Cross Product of two vectors a⨯b in 3-dimensional space results in a third vector that is perpendicular to both.

The CROSS function calculates the cross product by first converting row vectors to column vectors, and if either of the vectors has a length of 2, it adds a zero as the 3rd element. This is based on the assumption that if a vector has only two elements specified, then a = a₁*i + a₂*j + 0*k, using (i, j, k) for coordinate notation.

Formula for the Cross Product:

[ i  j  k 
  a1 a2 a3
  b1 b2 b3 ]
 
a⨯b = (a₂b₃-a₃b₂)i + (a₃b₁-a₁b₃)j + (a₁b₂-a₂b₁)k

Right-Hand Rule: The direction of the Cross Product follows the right-hand rule: point your fingers in the direction of vector a, then curl your fingers toward vector b with your thumb up. Your thumb points in the direction of the cross product.

Properties of the Cross Product

If the Cross Product is 0, then either one or both of a and b are 0 or they are parallel or antiparallel.

The Cross Product c=a⨯b is perpendicular to both vectors a and b, so DOT(c,a)=0 and DOT(c,b)=0.

The magnitude of the Cross Product is equal to the area of the parallelogram outlined by a+b-a-b.

See the Wikipedia article for more details.

Lambda Formula

This code for using CROSS in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.

Code to Create Function via the Name Manager

Name: CROSS
Comment: Returns the Cross Product of two vectors
Refers To:

=LAMBDA(vector_a,vector_b,
LET(doc,"https://www.vertex42.com/lambda/cross.html",
    toColVec3,LAMBDA(vec,LET(
        cvec,IF(AND(ROWS(vec)=1,COLUMNS(vec)>1),TRANSPOSE(vec),vec),
        IF(ROWS(cvec)=2,VSTACK(cvec,0),cvec)
    )),
    vec_a,toColVec3(vector_a),
    vec_b,toColVec3(vector_b),
    arr,VSTACK(TRANSPOSE(vec_a),TRANSPOSE(vec_b)),
    IF( OR(ROWS(vec_a)<>3,ROWS(vec_b)<>3,COLUMNS(vec_a)>1,COLUMNS(vec_b)>1),
        "Error in vector size",
        VSTACK(
            MDETERM(CHOOSECOLS(arr,2,3)),
            MDETERM(CHOOSECOLS(arr,3,1)),
            MDETERM(CHOOSECOLS(arr,1,2))
        )
    )
))

Code for AFE Workbook Module (Excel Labs Add-in)

/**
* Returns the Cross Product of two 3x1 vectors a and b.
*/
CROSS = LAMBDA(vector_a,vector_b,
LET(doc,"https://www.vertex42.com/lambda/cross.html",
    toColVec3,LAMBDA(vec,LET(
        cvec,IF(AND(ROWS(vec)=1,COLUMNS(vec)>1),TRANSPOSE(vec),vec),
        IF(ROWS(cvec)=2,VSTACK(cvec,0),cvec)
    )),
    vec_a,toColVec3(vector_a),
    vec_b,toColVec3(vector_b),
    arr,VSTACK(TRANSPOSE(vec_a),TRANSPOSE(vec_b)),
    IF( OR(ROWS(vec_a)<>3,ROWS(vec_b)<>3,COLUMNS(vec_a)>1,COLUMNS(vec_b)>1),
        "Error in vector size",
        VSTACK(
            MDETERM(CHOOSECOLS(arr,2,3)),
            MDETERM(CHOOSECOLS(arr,3,1)),
            MDETERM(CHOOSECOLS(arr,1,2))
        )
    )
));

Named Function for Google Sheets

Name: CROSS
Description: Returns the Cross Product of two vectors
Arguments: vector_a, vector_b
Function:

=LET(doc,"https://www.vertex42.com/lambda/cross.html",
    toColVec3,LAMBDA(vec,LET(
        cvec,IF(AND(ROWS(vec)=1,COLUMNS(vec)>1),TRANSPOSE(vec),vec),
        IF(ROWS(cvec)=2,VSTACK(cvec,0),cvec)
    )),
    vec_a,toColVec3(vector_a),
    vec_b,toColVec3(vector_b),
    arr,VSTACK(TRANSPOSE(vec_a),TRANSPOSE(vec_b)),
    IF( OR(ROWS(vec_a)<>3,ROWS(vec_b)<>3,COLUMNS(vec_a)>1,COLUMNS(vec_b)>1),
        "Error in vector size",
        VSTACK(
            MDETERM(CHOOSECOLS(arr,2,3)),
            MDETERM(CHOOSECOLS(arr,3,1)),
            MDETERM(CHOOSECOLS(arr,1,2))
        )
    )
)

CROSS Examples

Example 1

Find the Cross Product of vectors a={1;0;0} and b={0;1;0}

Test: Copy and Paste this LET function into a cell
=LET(
    vector_a, {1;0;0},
    vector_b, {0;1;0},
    CROSS(vector_a,vector_b)
)

Result: {0;0;1}

CROSS