Mrinmoy Roy
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.
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.
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.
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.
- To create a free Bing MAP API, click here.
I’ve created an API. I’m attaching the API below:
AhFG0hk5nKCcQlk80MRaSk1ZtoYUYsX98BCLWi7p7MKZ-VrzOWptdUwsvj9D3L9F
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
Code Breakdown
- Here, I’ve created a user-defined function called Driving_Distance.
- Then I used 3 parameters: startlocation, destination, and keyvalue. These are the position of the two addresses and the API value respectively.
- Then I used several variables such as First_Value, Second_Value, Last_value, mitHTTP, & mitUrl. These variables are used to store different values.
- Then combined the values (stored within mitUrl) and used several object methods (Open, SetRequestHeader, Send). 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.
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
- How to Calculate Distance Between Two Cities in Excel
- Calculate Distance between Two GPS Coordinates in Excel
- How to Calculate Distance in Excel with Google Maps
- Calculate Coordinates from Bearing and Distance in Excel
- How to Calculate Mahalanobis Distance in Excel (Step by Step)
- Calculate Distance Between Two Coordinates in Excel (2 Methods)
- How to Calculate Manhattan Distance in Excel (2 Suitable Ways)
Hi,
I am interested in the second solution: Calculate Driving Distance between Two Addresses Using VBA Code, but i would need to know the distance in KILOMETERS and using a bing map API. Could you help me with that perhaps?
Thanks,
Bianka
Hi BIANKA,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. If you want to get the distance in KILOMETERS then you can use the following VBA code and the rest of the procedures will be the same.
I hope this will help you to solve your problem. Please let us know if you have other queries.
Regards
Mashhura Jahan
ExcelDemy.
This was great. Thank you.
Do you know if there is a way to get driving time?
Hi JORGE,
You asked for an interesting extension of this documentation. It’s all the same. Just have to make some modifications. Follow the code below:
Here, we renamed the function to Driving_Time. And changed the mitUrl variable.
Also, we modified the last line of the code to:
Here, we divided with 60 to get the time in minutes. Otherwise, it’ll return the time in seconds.
Hope, you find it helpful. Btw, are you Spanish? I like the spelling of your name. Thanks again.
Regards,
SHAHRIAR ABRAR RAFID
Team ExcelDemy
Hello, very interesting article. Thanks for taking the time to put this up. I have a couple of quick questions. How can one build the API Key into the VBA code so that you do not have to put it into a cell in Excel? Also, where can I put the VBA code so that it is available to any workbook I create?
Thanks!
Thank you, Jeb, for your query. You can take the API as a String value to your VBA code. Thus you don’t need to put the API value in your Excel sheet. So the existing code given in the workbook here can be modified as follows:
Here we have changed the first two line of the given code. The previous code was as like:
But we have removed the third argument which represents the API value. Alternately, We declare the variable outside and put the API as a string value.
Now here comes to your second question. You have to create a customized Add-in to store your VBA code and therefore allow you to execute the code in any workbook. Hope you get your answer. However, if you have any further query, please let me know.