Calculating Distance Between Two Places in Excel

To calculate the distance between two geographical locations based on their latitude and longitude coordinates, the Haversine formula is commonly used. This formula accounts for the Earth's curvature and provides the distance in kilometers or miles.

Haversine Formula in Excel

The general structure of the Haversine formula is:

d = 2 * r * arcsin(sqrt(hav(Δlat) + cos(lat1) * cos(lat2) * hav(Δlong)))

Where:

• d is the distance between the two points (along the surface of the sphere).
• r is the Earth's radius (mean radius = 6,371 kilometers or 3,959 miles).
• lat1, lon1 are the latitude and longitude of the first location.
• lat2, lon2 are the latitude and longitude of the second location.
• Δlat is lat2 - lat1.
• Δlong is lon2 - lon1.
• hav is the haversine function: hav(θ) = sin²(θ/2).

Excel Formula Implementation

Assuming:

• A1 contains Latitude 1
• B1 contains Longitude 1
• A2 contains Latitude 2
• B2 contains Longitude 2

You can use the following Excel formula to calculate the distance in kilometers:

=6371 * ACOS(SIN(RADIANS(A1)) * SIN(RADIANS(A2)) + COS(RADIANS(A1)) * COS(RADIANS(A2)) * COS(RADIANS(B2) - RADIANS(B1)))

Explanation of the Formula

1. RADIANS Function: Converts degrees to radians because trigonometric functions in Excel require angles in radians.
2. SIN and COS Functions: Calculate the sine and cosine of the given latitudes and the difference in longitudes.
3. ACOS Function: Returns the arccosine, which is the angle corresponding to the cosine value computed.
4. 6371: This is the average radius of the Earth in kilometers, which allows the result to be the distance between the two points in kilometers.

Practical Example

Assume the following coordinates:

• Location 1 (City A):
  • Latitude: 40.7128 (New York City)
  • Longitude: -74.0060
• Location 2 (City B):
  • Latitude: 34.0522 (Los Angeles)
  • Longitude: -118.2437

Input these values into the respective cells:

• A1: 40.7128
• B1: -74.0060
• A2: 34.0522
• B2: -118.2437

Using the formula in another cell, you will get the distance in kilometers between New York City and Los Angeles.

Conclusion

This method efficiently calculates the distance between two geographical locations using their latitude and longitude in Excel. For users looking to deepen their understanding of data analysis techniques, consider exploring the Enterprise DNA Platform for additional resources and courses.