How to Calculate Euclidean Distance in Excel


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

Euclidean distance = √Σ(Ai-Bi)2

where:

  • Σ is a Greek symbol that means “sum”
  • Ai is the ith value in vector A
  • Bi is the ith 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:

Euclidean distance in Excel

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:

Euclidean distance in Excel example

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

Leave a Reply

Your email address will not be published.