DOT Product Function for Excel

=DOT(a, b)

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

Download the Template

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

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}

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

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, DOT returns a row vector containing the dot product of corresponding columns. Another way to say this is that 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}

DOT(A,B) = 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 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 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: 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
*/
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: 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.

DOT Examples

Example 1

Test the example shown in the above description. 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},
    DOT(xMat,yMat)
)

Result: {30, 120, 270}

DOT - Dot Product