Distance Formula in Excel (4 Examples to Calculate)

In this article, we will learn how to find distance using formulas in Excel. We will apply the basic arithmetic formula, the Euclidean formula, the Haversine formula, and a VBA user-defined function (UDF) to calculate the distance in Excel. We will use Cartesian coordinates for point distance and GPS coordinates for the distance between two places.

While preparing this article, we used Excel 365 for applying all operations, but they are also applicable in all Excel versions.

In our daily life, we frequently need to calculate the distance between two points or places. We can easily calculate distance using the latitude and longitude of two places. It helps to determine the distance between two geographic locations. We can also calculate distance using cartesian coordinates.

This is an image containing the GPS coordinates of two different states in the USA. It also contains the Haversine formula. We will use this formula to get the distance between these two places.

How to Find Distance Using Formula in Excel?

There are four ways to find distance using formula in Excel. Read the methods below to learn all of them.

1. Calculating Distance Using the Basic Arithmetic Formula

To calculate the distance in a cartesian coordinate system, we can apply the SQRT function in Excel.

Follow the steps below to do it yourself.

We will measure the distance between two points using the basic arithmetic formula. We have a dataset of a few points with their Cartesian coordinates.

Now, we will insert the formula with the SQRT function in cell G6 and drag down the Fill Handle tool.

=SQRT((E6-C6)^2 + (F6-D6)^2)

Finally, you will get the distance between each pair of points.

2. Calculating Euclidean Distance in Excel

The distance between two vectors is known as the Euclidean distance. The formula to find the Euclidean distance is:

Euclidean distance = √Σ(X-Y)2

Here,
Σ Greek sign means Total Sum.
X is the value in vector point 1.
Y is the value in vector point 2.

We can use this formula in the following format in Excel.

=SQRT(SUMXMY2(ARRAY 1, ARRAY 2))

Follow the next steps to calculate the distance using the Euclidean formula.

● In the beginning, we have a dataset with few vector points.

● Next, insert the following Euclidean formula in cell C11.

=SQRT(SUMXMY2(B5:B10,C5:C10))

Formula Breakdown

● The SUMXMY2 function returns the sum of squares of differences of corresponding values in two arrays. Here, it returns that for Point 1 and Point 2 arrays.
● The SQRT function provides the square root value of a number. Here, it will return the square root value of the sum.

Finally, you will see the Euclidean distance in cell C11.

Note: This function calculates only pairwise data, it ignores single values in the array.

3. Finding Distance Between Two Coordinates Applying the Haversine Formula

The Haversine formula calculates the great circle distance between two points on the surface of a sphere. We can calculate the distance between two places using this formula.

Let’s go through the Haversine Formula’s parameters.

φ1 = Latitude of the first place

φ2 = Latitude of the second place

ℽ1 = Longitude of the first place

ℽ2 = Latitude of the second place

Now, follow the steps mentioned below to find the distance using the Haversine formula.

First, we have a dataset containing the GPS coordinates of two addresses.

Now, insert the following Haversine formula in any other cell (here, cell D8).

=2*6400*ASIN(SQRT((SIN(RADIANS((C6-C5)/2)))^2+COS(RADIANS(C5))*COS(RADIANS(C6))*(SIN(RADIANS((D6-D5)/2)))^2))

Formula Breakdown

ASIN: The ASIN function gives the arcsine of the inverse sine of a value.
SQRT: The SQRT function returns the square root of any number.
RADIANS: The RADIANS function converts degree values to radians.
SIN: The SIN function calculates the sine of a value. The SIN function is used twice in the formula.
SIN(C6-C5) returns the sine of the difference between the values in cells C6 and C5.
SIN(D6-D5) gives the sine of the difference between the values in cells D6 and D5.
COS: The COS function calculates the cosine of a value. Here it is used to calculate: COS(C5) and COS(C6)
Arithmetic operations: The formula includes several arithmetic operations:
- / represents division.
- ^2 represents squaring a value.
- * represents multiplication.
Constants: The formula has two constant values. One is number 2. Another one is 6400, which is the radius of Earth in kilometers.

Finally, you will get the distance between two places using the Haversine formula. You can also find the distance in miles.

Note: Remember that the formula was developed under the assumption that the earth had a spherical shape, even though it has an elliptical shape. Therefore, there may be a difference between the distance determined by the method and the findings of Google Maps or other web apps. Please use the other methods to obtain accurate results.

4. Creating UDF to Calculate Distance Using VBA Code

Yes, we can create a user-defined function(UDF) in VBA to calculate distance in Excel. Read the steps to learn that.

Here, we have the GPS coordinates of a few locations.

Now, we will open the Developer tab and select the Visual Basic command.

After that, from the Insert option, we will choose the new Module to write the VBA code.

Now, we will insert the VBA code and run the module as mentioned below.

Public Function CalculateDistance (Latitude1 As Double, Longitude1 As Double, _
    Latitude2 As Double, Longitude2 As Double) As Double
    With WorksheetFunction
        Dim P As Double
        Dim Q As Double
        Dim R As Double
        Dim S As Double
        Dim T As Double
        P = Cos(.Radians(90 - Latitude1))
        Q = Cos(.Radians(90 - Latitude2))
        R = Sin(.Radians(90 - Latitude1))
        S = Sin(.Radians(90 - Latitude2))
        T = Cos(.Radians(Longitude1 - Longitude2))
        ' Change 3959 to 6371 to get the result in kilometers
        CalculateDistance = .Acos(P * Q + R * S * T) * 3959
    End With
End Function

Code Breakdown

Public Function CalculateDistance (Latitude1 As Double, Longitude1 As Double, Latitude2 As Double, Longitude2 As Double) As Double

This line begins a public function named CalculateDistance that takes four input parameters: Latitude1, Longitude1, Latitude2, and Longitude2, all of which are Double data types. The function will return the distance between the two points as a Double data type.

With WorksheetFunction

This line begins a With a block that allows us to access built-in worksheet functions using the WorksheetFunction object.

P = Cos(.Radians(90 - Latitude1))
Q = Cos(.Radians(90 - Latitude2))
R = Sin(.Radians(90 - Latitude1))
S = Sin(.Radians(90 - Latitude2))
T = Cos(.Radians(Longitude1 - Longitude2))

These codes assign values to the variables P, Q, R, S, and T. Here, the code uses trigonometric functions and the Radians methods to convert degrees to radians.

CalculateDistance = .Acos(P * Q + R * S * T) * 3959

The code calculates the distance between the two points using the Haversine formula. The result is assigned to the CalculateDistance variable.

The Acos function returns the inverse cosine of the total of P * Q and R * S * T.

Now multiply the value by 3959 to get the distance in miles. You can adjust 3959 to 6371 to get the answer in kilometers.

Now, apply the UDF (user-defined function) in the dataset.

Now, drag down or double-click the Fill Handle tool to copy the VBA function to the bottom cells.

Which Things You Have to Keep in Mind?

Make sure the coordinates are formatted correctly. You can use both Geodetic and Cartesian coordinates.
Ensure that the units of the coordinate are consistent. If you use different units (for example, feet and meters), the calculated distance will not be accurate. Convert the coordinates to the same unit before using the formula.
Maintaining the order of the points in the formula is important. Suppose the formula is in the following order: (x1, y1) and (x2, y2). If you reverse the order, the result will be inaccurate.

Download Practice Workbook

Distance Formula.xlsm

Conclusion

In this article, we have shown how to find the distance between two points or places using different formula in Excel. We used the basic mathematical formula, Euclidean formula, Haversine formula, and VBA. We hope now you will be able to calculate distance in Excel. Practice these formulas by yourself and become an expert in Excel! If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

Frequently Asked Questions

1. Can I calculate the distance between two points in different worksheets?

Answer: You can calculate the distance between two points on different worksheets. In the distance formula, reference the coordinates’ cells from the appropriate worksheets.

2. How can I calculate the distance between multiple points in Excel?

Answer: You can find the distance between two points by using the distance formula with the array formula or helper columns.

3. Is there a built-in function in Excel to calculate distances between coordinates?

Answer: Excel doesn’t have a built-in function for calculating distances between coordinates. However, you can calculate distances using the distance mentioned above, which combines functions such as SQRT and POWER.