How to calculate distance between two Latitude and Longitude - Excel

How to Calculate the Distance Between Two Latitude and Longitude Points Using Excel

Learn how to calculate the distance between two latitude and longitude points using Excel with the Haversine formula. Our step-by-step guide simplifies the process for accurate distance computation.

In today’s data-driven world, geographic coordinates such as latitude and longitude are crucial for various applications like logistics, GPS navigation, mapping, and location-based services. One common task when working with geographic data is to calculate the distance between two points based on their latitude and longitude.

Fortunately, Excel makes it relatively easy to perform this calculation without the need for advanced mapping software. This article will walk you through how to calculate the distance between two latitude and longitude coordinates using Excel.

Understanding Latitude and Longitude

Before diving into the Excel formulas, it’s essential to understand the concept of latitude and longitude:

• Latitude refers to how far north or south a point is from the Equator, measured in degrees.
• Longitude refers to how far east or west a point is from the Prime Meridian, also measured in degrees.

These coordinates form a grid system, where each location on the earth can be pinpointed using a unique combination of latitude and longitude.

The Haversine Formula: Calculating Distance Between Two Points

The most common formula to calculate the distance between two points on the Earth’s surface using their latitude and longitude is the Haversine formula. It takes into account the spherical shape of the Earth and provides the shortest distance (often referred to as the "great-circle distance") between two points.

Haversine Formula:

The formula to calculate the distance (d) between two points is as follows:

a = sin²(Δφ/2) + cos φ1 * cos φ2 * sin²(Δλ/2) c = 2 * atan2(√a, √(1−a)) d = R * c

Where:

• φ1 and φ2 are the latitudes of the two points in radians.
• λ1 and λ2 are the longitudes of the two points in radians.
• R is the Earth’s radius (mean radius = 6,371 km).
• Δφ = φ2 − φ1 (the difference in latitude).
• Δλ = λ2 − λ1 (the difference in longitude).

Step-by-Step Guide: Implementing the Haversine Formula in Excel

Here’s how you can calculate the distance between two sets of latitude and longitude coordinates in Excel:

1. Input Your Latitude and Longitude Data

Let’s assume you have the following coordinates:

• Point 1: Latitude (A2), Longitude (B2)
• Point 2: Latitude (A3), Longitude (B3)

You’ll enter these values in your Excel sheet.

2. Convert Degrees to Radians

Since the Haversine formula requires latitudes and longitudes in radians, you must first convert the degree values into radians.

To convert degrees to radians, use the following formula in Excel:

=RADIANS(degrees)

For example, to convert the latitude of Point 1 (cell A2) into radians, you would use the formula:

=RADIANS(A2)

3. Calculate the Differences in Latitudes and Longitudes

Now, calculate the differences between the latitudes and longitudes of the two points:

• Difference in Latitude (Δφ):

=RADIANS(A3) - RADIANS(A2)

• Difference in Longitude (Δλ):

=RADIANS(B3) - RADIANS(B2)

4. Apply the Haversine Formula

Now that you have the values in radians and the differences, you can apply the Haversine formula step-by-step.

• First, calculate a using the formula:

=a = SIN(Δφ/2)^2 + COS(RADIANS(A2)) * COS(RADIANS(A3)) * SIN(Δλ/2)^2

This can be written in Excel as:

=SIN((RADIANS(A3) - RADIANS(A2))/2)^2 + COS(RADIANS(A2)) * COS(RADIANS(A3)) * SIN((RADIANS(B3) - RADIANS(B2))/2)^2

• Then, calculate c using the formula:

=2 * ATAN2(SQRT(a), SQRT(1-a))

• Finally, multiply c by the Earth’s radius (R = 6371 km) to get the distance in kilometers:

=6371 * (2 * ATAN2(SQRT(a), SQRT(1-a)))

5. Combine Everything into One Excel Formula

You can combine all the steps into a single Excel formula. Here’s the complete formula to calculate the distance between two points in kilometers:

=6371 * 2 * ATAN2(SQRT(SIN((RADIANS(A3) - RADIANS(A2))/2)^2 + COS(RADIANS(A2)) * COS(RADIANS(A3)) * SIN((RADIANS(B3) - RADIANS(B2))/2)^2), SQRT(1 - (SIN((RADIANS(A3) - RADIANS(A2))/2)^2 + COS(RADIANS(A2)) * COS(RADIANS(A3)) * SIN((RADIANS(B3) - RADIANS(B2))/2)^2)))

This formula will calculate the distance in kilometers based on the latitude and longitude values from your Excel cells.

Conclusion

By using the Haversine formula, you can easily calculate the distance between two geographical points in Excel based on their latitude and longitude coordinates. Whether you're working with large datasets or just need a quick calculation, this method offers an efficient solution for distance computation.

Excel’s built-in functions, such as RADIANS, SIN, COS, and ATAN2, make it simple to implement even complex mathematical formulas like the Haversine formula directly in your spreadsheet.

For those working with logistics, location-based services, or any field where distance calculations are essential, this Excel method provides a powerful tool to enhance your productivity and precision.