DOT Product Function for Excel

Work in Progress :: Please contact us to report errors, typos, etc.

=L_DOT(a, b)

Argument	Description	Example
a	A vector or matrix	2
b	A vector or matrix the same size as a	4

Description

The Dot Product of two vectors is the sum of the element-wise multiplication of the two vectors. For two column vectors, this is exactly the same as using the SUMPRODUCT function.

a = {a1; a2; a3}
b = {b1; b2; b3}

L_DOT(a,b) = a1*b1 + a2*b2 + a3*b3

L_DOT(a,b) = SUMPRODUCT(a,b)

Note

In Excel and Google Sheets, the * operator performs element-wise multiplication (as opposed to matrix multiplication using MMULT).

When A and B are two identically sizes matrices, L_DOT returns a row vector containing the dot product of corresponding columns. Another way to say this is that L_DOT returns the column sum of the element-wise multiplication A*B. This is not the way SUMPRODUCT works (which is the total sum of A*B).

[ 1  2  3
  1  2  3
  1  2  3 ]
  
A = {1,2,3; 1,2,3; 1,2,3}

[ 10 20 30 
  10 20 30 
  10 20 30 ]
  
B = {10,20,30; 10,20,30; 10,20,30}

L_DOT(A,B) = L_COLSUM(A*B)
           = {1*10+1*10+1*10, 2*20+2*20+2*20, 3*30+3*30+3*30}
           = {30, 120, 270}

The L_DOT function has some logic included to handle invalid inputs. First, if either a or b is a row vector, it is first converted (using TRANSPOSE) to a column vector. Then, if a and b are not the same size, the function returns an error.

Properties of the DOT Product

The Dot Product of two orthogonal (perpendicular) vectors is 0 (zero).

The square root of the Dot Product of a vector with respect to itself is the Magnitude of the vector.

Lambda Formula

This code for using L_DOT 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: L_DOT
Comment: Returns the dot product of two vectors
Refers To:

=LAMBDA(a,b,
LET(doc,"https://www.vertex42.com/lambda/dot.html",
    a,IF(AND(ROWS(a)=1,COLUMNS(a)>1),TRANSPOSE(a),a),
    b,IF(AND(ROWS(b)=1,COLUMNS(b)>1),TRANSPOSE(b),b),
    IF(OR(ROWS(a)<>ROWS(b),COLUMNS(a)<>COLUMNS(b)),
        "Error: a and b must be the same size",
        BYCOL(a*b,LAMBDA(col,SUM(col)))
    )
))

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

/**
* Returns the sum of the element-wise multiplication of two vectors
* or the column sum of the element-wise multiplication of two matrices
*/
L_DOT = LAMBDA(a,b,
LET(doc,"https://www.vertex42.com/lambda/dot.html",
    a,IF(AND(ROWS(a)=1,COLUMNS(a)>1),TRANSPOSE(a),a),
    b,IF(AND(ROWS(b)=1,COLUMNS(b)>1),TRANSPOSE(b),b),
    IF(OR(ROWS(a)<>ROWS(b),COLUMNS(a)<>COLUMNS(b)),
        "Error: a and b must be the same size",
        BYCOL(a*b,LAMBDA(col,SUM(col)))
    )
));

Named Function for Google Sheets

Name: L_DOT
Description: Returns the dot product of two vectors
Arguments: a, b
Function:

=LET(doc,"https://www.vertex42.com/lambda/dot.html",
    a,IF(AND(ROWS(a)=1,COLUMNS(a)>1),TRANSPOSE(a),a),
    b,IF(AND(ROWS(b)=1,COLUMNS(b)>1),TRANSPOSE(b),b),
    IF(OR(ROWS(a)<>ROWS(b),COLUMNS(a)<>COLUMNS(b)),
        "Error: a and b must be the same size",
        BYCOL(ARRAYFORMULA(a*b),LAMBDA(col,SUM(col)))
    )
)

Warning

These functions are not compatible between Excel and Google Sheets. When using these functions, you will not be able to convert the Excel file to Google Sheets or vice versa without problems.

L_DOT Examples

Example 1

Test the example shown in the above description. L_DOT returns the column sum of X*Y when X and Y are the same size matrices.

Test: Copy and Paste this LET function into a cell
=LET(
    xMat, {1,2,3; 1,2,3; 1,2,3},
    yMat, {10,20,30; 10,20,30; 10,20,30},
    L_DOT(xMat,yMat)
)

Result: {30, 120, 270}

L_DOT