Mrinmoy Roy
The Euclidean distance is calculated for two vector points on a plane. However, the Euclidean distance is the distance between two locations in coordinate geometry. It is calculated based on the Pythagoras theorem. You can use some built-in functions to calculate the Euclidean distance in Excel quite easily. In this article, I will show you how to calculate Euclidean distance in Excel with ease.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
What Is Euclidean Distance?
Euclidean distance is the distance between two vector points on a plane. For example, A & B are two distinct points on a plane. The three-dimensional coordinates of these two points are A(X1, Y1, Z1) and B(X2, Y2, Z2) respectively. Then the Euclidean distance, AB between A & B is the difference between the coordinates of the A & B. And the direction of the distance will be from A to B.
Euclidean Distance Formula
If A(X1, Y1, Z1) and B(X2, Y2, Z2) are two vector points on a plane. Then the Euclidean distance between A & B can be calculated from the following formula:
AB= √((X1−X2 )^2+(Y1−Y2 )^2+(Z1−Z2 )^2))In this formula,
- X1, Y1, and Z1 are the coordinates of A.
- X2, Y2, and Z2 are the coordinates of B.
2 Methods to Calculate Euclidean Distance in Excel
I will use the following dataset to show you to calculate the Euclidean distance between two points in Excel. In the dataset, you will find 3 columns, Axis, Point A, and Point B. Point A and Point B columns contain the corresponding coordinate values of the two points whose Euclidean distance we are about to calculate.
1. Using SUM & SQRT Functions to Calculate Euclidean Distance
At first, I will use two functions, the SUM, and the SQRT to calculate the Euclidean distance between Point A and Point B.
The syntax of the formula is,
=SQRT(SUM((number1,[number2],...))^2)In this case, instead of using distinct numbers inside the SUM function, I will use the range of numbers. Thus, the formula will be so compact to use.
Now follow the steps below to calculate the Euclidean distance.
❶ First of all, insert the following formula in cell D9.
=SQRT(SUM((C5:C7-D5:D7))^2)Formula Breakdown
- C5:C7: This range refers to the coordinates of point A.
- D5:D7: This range refers to the coordinates of point B.
- SUM((C5:C7-D5:D7))^2: The SUM function sums up the square of the differences between the coordinates of point A and point B. If I expand this part, it becomes SUM((C5-D5)^2+(C6-D6)^2+(C7-D7)^2).
- Finally, the SQRT function returns the square root of the value returned by SUM((C5:C7-D5:D7))^2.
❷ Now press the ENTER button.
After that, you will get the Euclidean distance between point A and point B in cell D9. Here, the calculated Euclidean distance is 34.
Read More: How to Calculate Distance Between Two Coordinates in Excel (2 Methods)
2. Calculate Euclidean Distance Using SQRT & SUMXMY2 Functions
In the previous formula, I used the SUM function to distinctly calculate the square of the difference between the coordinates of point A and point B. Well, this method is not compulsory when Excel has a built-in function called SUMXMY2; which does the exact same job.
In this method, I will show you how to calculate the Euclidean distance between two points using the SQRT & SUMXMY2 functions.
So the syntax of the formula is:
=SQRT(SUMXMY2(array_x, array_y))So, in this formula, you just need to insert the coordinates of point A and point B respectively. The formula itself will calculate the rest.
Now follow the steps below to calculate the Euclidean distance between two points.
❶ At first, insert the following formula in cell D9.
=SQRT(SUMXMY2(C5:C7,D5:D7))Formula Breakdown
- C5:C7: This range refers to the coordinates of point A.
- D5:D7: This range refers to the coordinates of point B.
- SUMXMY2(C5:C7,D5:D7): The SUMXMY2 function sums up the square of the differences between the coordinates of point A and point B. If I expand this part, it becomes (C5-D5)^2+(C6-D6)^2+(C7-D7)^2.
- Finally, the SQRT function returns the square root of the value returned by SUMXMY2(C5:C7,D5:D7).
❷ Now press the ENTER button.
After that, you will get the Euclidean distance between point A and point B in cell D9. Here, the calculated Euclidean distance is 34.78505426.
Read More: How to Calculate Distance between Two GPS Coordinates in Excel
Practice Section
You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the methods discussed in this article.
Conclusion
To sum up, we have discussed 2 ways to calculate Euclidean distance in Excel. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.
Related Articles
- How to Calculate Distance Between Two Cities in Excel
- Calculate Coordinates from Bearing and Distance in Excel
- How to Calculate Distance Between Two Addresses in Excel (3 Ways)
- Calculate Mahalanobis Distance in Excel (Step by Step)
- How to Calculate Levenshtein Distance in Excel (4 Easy Methods)
- Calculate Driving Distance between Two Addresses in Excel
