Excel Formula for Distance and Driving Time Between Two Coordinates

This document outlines how to calculate the distance and estimated driving time between two geographic coordinates (latitude and longitude) in Excel using built-in functions and a web service API for driving time.

1. Calculation of Distance Between Two Coordinates

a. Haversine Formula

To calculate the great-circle distance between two points on the Earth specified by latitude and longitude, the Haversine formula can be used.

b. Excel Formula

Assuming:

• Latitude and Longitude of Point A are in cells A1 (Latitude A) and B1 (Longitude A).
• Latitude and Longitude of Point B are in cells A2 (Latitude B) and B2 (Longitude B).

The Excel formula for Haversine distance (in kilometers) is as follows:

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

c. Explanation of the Formula

• 6371: The Earth's radius in kilometers.
• RADIANS(): Converts degrees to radians, which is necessary for trigonometric functions in Excel.
• COS() and SIN(): Standard trigonometric functions used in the Haversine formula to compute the distance based on angular separation.

2. Estimation of Driving Time

a. Using a Web Service API

To obtain driving time, you can integrate with a mapping service like the Google Maps API. The API provides estimated driving time in seconds based on shortest route calculations.

b. Example Approach

1. Setup a Web Service Query: Use Excel's Power Query or a VBA script to interact with the API.

2. Formula for Driving Time Retrieval (VBA):

Assuming you have set up a valid API key and have access to the Google Maps Distance Matrix API, use the following VBA function:

Function GetDrivingTime(lat1 As Double, lon1 As Double, lat2 As Double, lon2 As Double) As Double
    Dim apiKey As String
    Dim url As String
    Dim http As Object
    Dim response As String
    Dim json As Object

    apiKey = "YOUR_API_KEY"
    url = "https://maps.googleapis.com/maps/api/distancematrix/json?origins=" & lat1 & "," & lon1 & "&destinations=" & lat2 & "," & lon2 & "&key=" & apiKey

    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", url, False
    http.send
    response = http.responseText

    Set json = JsonConverter.ParseJson(response)

    GetDrivingTime = json("rows")(1)("elements")(1)("duration")("value") / 60 ' time in minutes
End Function

c. Explanation of the VBA Function

• This function constructs a URL to make a request to the Google Maps API using origin and destination coordinates.
• It retrieves the response and parses it to extract the driving time in seconds, converting it to minutes.

3. Practical Example

a. Data Setup

b. Using the Haversine Formula

In a new cell:

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

c. VBA Driving Time (if implemented)

In the cell where you want the driving time, use:

=GetDrivingTime(A1, B1, A2, B2)

Conclusion

This document provides a structured approach to calculating the distance and estimated driving time between two coordinates using Excel. The Haversine formula gives the distance, while a VBA function can retrieve driving time from an external API.

For users wishing to enhance their Excel skills, consider exploring the Enterprise DNA Platform for specialized courses on data analysis and Excel functionality.