This tutorial explains how to calculate the dot product in Excel.
What is the Dot Product?
Given vector a = [a1, a2, a3] and vector b = [b1, b2, b3], the dot product of vector a and vector b, denoted as a · b, is given by:
a · b = a1 * b1 + a2 * b2 + a3 * b3
For example, if a = [2, 5, 6] and b = [4, 3, 2], then the dot product of a and b would be equal to:
a · b = 2*4 + 5*3 + 6*2
a · b = 8 + 15 + 12
a · b = 35
In essence, the dot product is the sum of the products of the corresponding entries in two vectors.
How to Find the Dot Product in Excel
To find the dot product of two vectors in Excel, we can use the followings steps:
1. Enter the data. Enter the data values for each vector in their own columns. For example, enter the data values for vector a = [2, 5, 6] into column A and the data values for vector b = [4, 3, 2] into column B:
2. Calculate the dot product. To calculate the dot product, we can use the Excel function SUMPRODUCT(), which uses the following syntax:
SUMPRODUCT(array1, [array2], …)
- array – the first array or range to multiply, then add.
- array2 – the second array or range to multiply, then add.
In this example, we can type the following into cell D1 to calculate the dot product between vector a and vector b:
This produces the value 35, which matches the answer we got by hand.
Note that we can use SUMPRODUCT() to find the dot product for any length of vectors. For example, suppose vector a and b were both of length 20. Then we could enter the following formula in cell D1 to calculate their dot product:
Potential Errors in Calculating the Dot Product
The function SUMPRODUCT() will return a #VALUE! error if the vectors do not have equal length.
For example, if vector a has length 20 and vector b has length 19, then the formula =SUMPRODUCT(A1:A20, B1:B19) will return an error.
The two vectors need to have the same length in order for the dot product to be calculated.
The following tutorials explain how to calculate a dot product in different statistical software: