Open In App

How to Easily Calculate the Dot Product in Excel?

Improve
Improve
Like Article
Like
Save
Share
Report

The dot product is the sum of the product of two vectors. For example, two vectors are v1 = [2, 3, 1, 7] and v2 = [3, 6, 1, 5]. The sum of the product of two vectors is 2 × 3 + 3 × 6 + 1 × 1  = 60. We can use the = SUMPRODUCT(Array1, Array2) function to calculate dot product in excel.  

Dot Product 

The dot product or scalar product is the sum of the product of the two equal length vectors. The general formula for the dot product is,

Let us consider two vectors p = [p1, p2, p3, …, pn] and [q1, q2, q3, …, qn]

The dot product is,

p.q = \sum_{i = 1}^{n}p_iq_i

p.q = p1q1 + p2q2 + p3q3 +… pnqn.

Example: Given two vectors a = [4, 3, 6, 5] and b = [3, 1, 1, 2]. Find the dot product of the two vectors?

Answer:

We are given two vectors,

a = [4, 3, 6, 5]

b = [3, 1, 1, 2]

We know that the dot product is,

a.b = a1b1 + a2b2 + a3b3 + a4b4.

a.b = 4*3 + 3*1 + 6*1 + 5*2 

a.b = 12 + 3 + 6 + 10

a.b = 31

Function for Dot Product in Excel 

The function used for calculating dot product in excel is = SUMPRODUCT(Array1, Array2). This function calculates the sum of the product of the two selected range cells. 

Note: SUMPRODUCT function can calculate the sum of the product of n arrays also. We are not viewing that case because the dot product is for two arrays only. 

For example,

Consider a data set of Force and Distance traveled. Calculate the Work done. 

Note: Work done is the dot product of force and distance. 

Dataset

Following are the steps: 

Step 1: Write function = SUMPRODUCT() in the cell C10. 

Writing-function

Step 2: Select the range in which you want to calculate the dot product. Press Enter.

Selecting-range

Step 3: We get 40 as the work done in cell C10. 

Answer-obtained

Errors in SUMPRODUCT function

If the length of the Array1 is not equal to the length of the Array2, then the SUMPRODUCT function shows an error. For example, if the selected range for Array2 in the below data set is C3:C6 whereas the selected range for Array1 is B3:B7. Then the SUMPRODUCT function illustrates an error. 

Illustrating-errorError-in-answer

Last Updated : 07 Mar, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads