Euclidean Distance is a simple formula for calculating the distance between two points in a two-dimensional space. To calculate Euclidean Distance in Excel, you can use the Pythagorean Theorem, which states that the square of the hypotenuse (the side opposite the right angle) of a right triangle is equal to the sum of the squares of the other two sides. To calculate the distance between two points in Excel, you will need to use the SQRT function to calculate the square root of the sum of the squares of the difference between the x-coordinates and y-coordinates of the two points. Once you have the square root, you will have the Euclidean Distance.
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:
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:
