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.
❶ 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.
- 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
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:
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
- 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.
Now, it’s time to apply the user-defined function. For that,
- Select cell E10.
- Then insert the following formula:
- Now, press ENTER.
- 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.
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.
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.
- 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)