How to Calculate Driving Distance between Two Addresses in Excel

Microsoft Excel is a very versatile spreadsheet program. It offers to do such a wide range of tasks that you can’t even imagine. You can even calculate the driving distance between two addresses in Excel. If you have a list of addresses to find the difference between them, you can of course use MS Excel. You can also calculate the distance manually. But that will be too time-consuming. As you have hundreds of thousands of distances to calculate. Thus in this article, I will show you how to calculate the driving distance between two addresses in Excel.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


2 Effective Ways to Calculate Driving Distance between Two Addresses in Excel

1. Using Trigonometric Functions to Calculate Driving Distance

Here, I will show you to combine different trigonometric functions to calculate the driving distance between two addresses in Excel.

To give you an example, I’ve taken two addresses. The first address is MacArthur Park, Camden NSW, Australia. Its latitude and longitude are 34.06312149 and -118.2783975 respectively. The second address is Jersey City, New Jersey, USA. Its latitude and longitude are 40.71799929 and -74.04276812 respectively.

Dataset to Calculate Driving Distance Between Two Addresses in Excel

Now, I will combine the ACOS, COS, SIN, & RADIANS functions to create a formula. The formula will effectively calculate the driving distance between two addresses in miles.

For that,

❶ Select cell D8 first.

❷ Then insert the following formula in the cell.

=ACOS(COS(RADIANS(90-C6)) *COS(RADIANS(90-C5)) +SIN(RADIANS(90-C6)) *SIN(RADIANS(90-C5)) * COS(RADIANS(D6-D5))) *3959

❸ After that, press the ENTER button.

Now, you will see that the formula has calculated the driving distance between the MacArthur Park, Camden NSW, Australia, and Jersey City, New Jersey, USA in miles. Thus, you will see the result in cell D8 which is 2445.270922 miles.

Calculate Driving Distance Between Two Addresses in Excel using trigonometric functions

Formula Breakdown

  • COS(RADIANS(90-C6)) *COS(RADIANS(90-C5)) – the RADIANS functions convert the values into radians and the COS function provides the cosine of the values, the cosines for latitude are multiplied then. Output – 0.365377540842758
  • COS(RADIANS(D6-D5)) – provides the cosine value for the longitude difference between the two addresses. Output – 0.716476936499882
  • SIN(RADIANS(90-C6)) *SIN(RADIANS(90-C5)) – calculates the diversion of longitudes from 90 radians and multiplied the sine values. Output – 0.627884682513118
  • SIN(RADIANS(90-C6)) *SIN(RADIANS(90-C5)) *COS(RADIANS(D6-D5)) – becomes 0.627884682513118 * 0.716476936499882. Output – 0.449864893802199
  • COS(RADIANS(90-C6)) *COS(RADIANS(90-C5)) +SIN(RADIANS(90-C6)) *SIN(RADIANS(90-C5)) *COS(RADIANS(D6-D5)) – becomes 0.365377540842758 * 0.449864893802199. Output – 0.815242434644958
  • Then the ACOS function arccosines the value. Output – 0.617648629071256
  • Finally, multiplying the value by 3959 – 0.617648629071256 *3959 provides the result in miles. Output – 2445.270922

Read More: How to Calculate Miles between Two Addresses in Excel (2 Methods)


2. Calculate Driving Distance between Two Addresses Using VBA Code

In this section, I will use a VBA code to create a user-defined function. Then I will use that function to calculate the driving distance between two addresses in Excel.

Here, I’m using two addresses. The first address is MacArthur Park, Camden NSW, Australia. Its latitude and longitude are 34.06312149 and -118.2783975 respectively. The second address is Jersey City, New Jersey, USA. Its latitude and longitude are 40.71799929 and -74.04276812 respectively.

At the I will generate coordinates for each of the addresses. A coordinate is a combination of latitude and longitude. To generate a coordinate,

  • Type the latitude of an address first.
  • Then insert a comma.
  • After that type the longitude of the same address.

So the coordinate of the first address is 34.0631214903094,-118.27839753751. And the coordinate of the second address is 40.7179992930381,-74.0427681204225.

computing coordinate to Calculate Driving Distance Between Two Addresses in Excel

The VBA code requires an API of a map to calculate the driving distance. The API stands for Application Programming Interface. You can use an API to connect either the Google Map or the Bing Map whatever you prefer.
But creating the Google Map API is paid. On the contrary, you can create an API of the Bing MAP for free of cost.

Thus, I’m using the Bing MAP API here.

I’ve created an API. I’m attaching the API below:

AhFG0hk5nKCcQlk80MRaSk1ZtoYUYsX98BCLWi7p7MKZ-VrzOWptdUwsvj9D3L9F

Creating API to Calculate Driving Distance Between Two Addresses in Excel

Now, it’s time to write the VBA code. For that,

  • Press ALT + F11 to open the VBA Editor.
  • Now go to Insert Module to open a new module.

After opening the VBA Editor, insert the following VBA code in the opened module.

Option Explicit

Public Function Driving_Distance(startlocation As String, destination As String, keyvalue As String)

Dim First_Value As String, Second_Value As String, Last_Value As String, mitHTTP As Object, mitUrl As String

First_Value = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
Second_Value = "&destinations="
Last_Value = "&travelMode=driving&o=xml&key=" & keyvalue & "&distanceUnit=mi"

Set mitHTTP = CreateObject("MSXML2.ServerXMLHTTP")

mitUrl = First_Value & startlocation & Second_Value & destination & Last_Value
mitHTTP.Open "GET", mitUrl, False
mitHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
mitHTTP.Send ("")
Driving_Distance = Round(Round(WorksheetFunction.FilterXML(mitHTTP.ResponseText, "//TravelDistance"), 3), 0)

End Function

Calculate Driving Distance Between Two Addresses in Excel Using VBA

Code Breakdown

  • Here, I’ve created a user-defined function called Driving_Distance.
  • Then I used 3 parameters: startlocationdestination, and keyvalue. These are the position of the two addresses and the API value respectively.
  • Then I used several variables such as First_ValueSecond_ValueLast_valuemitHTTP, & mitUrl. These variables are used to store different values.
  • Then combined the values (stored within mitUrl) and used several object methods (OpenSetRequestHeaderSend). This is how I managed to calculate the driving distance through the API.

This VBA code generates a user-defined function called Driving_Distance.

The function Driving_Distance requires 3 arguments in total.

Here’s the generic syntax of the Driving_Distance function.

=Driving_Distance(Start_Location_Coordinate,End_Location_Coordinate, API)

Now, it’s time to apply the user-defined function. For that,

  • Select cell E10.
  • Then insert the following formula:
=Driving_Distance(E5,E6,C8)
  • Now, press ENTER.

Formula Breakdown

  • E5 is the Start_Location_Coordinate.
  • E6 is the End_Location_Coordinate.
  • C8 is the API of the Bing MAP.

Calculate Driving Distance Between Two Addresses in Excel Using User Defined Function

The function calculates the driving distance between two addresses in miles. Check out cell E10. It will see the number, 2790.

So the driving distance between MacArthur Park, Camden NSW, Australia, and Jersey City, New Jersey, USA is 2790 miles.

Read More: How to Calculate Distance Between Two Addresses in Excel (3 Ways)


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, I have discussed 2 ways to calculate the driving distance between two addresses 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

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo