Table of Contents
Euclidean Distance is a mathematical concept used to measure the distance between two points in a multi-dimensional space. In Excel, this distance can be calculated using the built-in function called “EUCLID.DIST”. This function takes the coordinates of the two points as input and returns the distance between them. It is important to note that the coordinates must be arranged in separate columns or rows in Excel for the function to work accurately. By using the “EUCLID.DIST” function, one can easily calculate the Euclidean Distance between any two points in an Excel spreadsheet, making it a useful tool for data analysis and visualization.
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
- Biis 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:
