How to Calculate Euclidean Distance in Excel

The Euclidean distance between two vectors, A and B, is calculated as:

Euclidean distance = √Σ(A_i-B_i)²

where:

• Σ is a Greek symbol that means “sum”
• A_i is the i^th value in vector A
• B_i is the i^th value in vector B

To calculate the Euclidean distance between two vectors in Excel, we can use the following function:

=SQRT(SUMXMY2(RANGE1, RANGE2))

Here’s what the formula does in a nutshell:

• SUMXMY2 finds the sum of the squared differences in the corresponding elements of range 1 and range 2.
• SQRT takes the square root of this sum of squared differences.

The end result if the Euclidean distance between the two ranges.

For example, suppose we have the following two vectors, A and B, in Excel:

We can use the following function to calculate the Euclidean distance between the two vectors:

The Euclidean distance between the two vectors turns out to be 12.40967.

Note that this function will only include complete pairwise observations when calculating the Euclidean distance.

For example, the last two rows in column A would not be included in the calculation of the Euclidean distance between the following two vectors:

The Euclidean distance between the two vectors turns out to be 5.656854. 

Additional Resources

How to Calculate Euclidean Distance in R
How to Calculate Euclidean Distance in Python