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

Get FREE Advanced Excel Exercises with Solutions!

Excel has a wide range of applications. It has a lot of practical uses, like if you want to find the distance between two addresses or multiple addresses then you can do it in Excel using some tricky ways. This article will provide you with 2 quick methods to calculate miles between two addresses in Excel.


Download Practice Workbook

You can download the free Excel workbook from here and practice on your own.


2 Ways to Calculate Miles between Two Addresses in Excel

Here’s the dataset that we are gonna use to apply the methods. It represents two places’ latitudes and longitudes which I have collected from Google Maps.


1. Using Latitude and Longitude to Calculate Miles between Two Addresses

In our first method, we’ll use the latitude and longitude within a formula. The formula will use some trigonometric functions- ACOS, SIN, COS, and RADIANS functions to determine distance as miles.

Steps:

  • Activate Cell D8.
  • Then type the following formula in it-
=ACOS(COS(RADIANS(90-C6)) *COS(RADIANS(90-C5)) +SIN(RADIANS(90-C6)) *SIN(RADIANS(90-C5)) *COS(RADIANS(D6-D5))) *3959
  • Finally, just press the ENTER button to get the distance in the Miles unit.

Using Latitude and Longitude to Calculate Miles between Two Addresses in Excel

Formula Breakdown:

  • COS(RADIANS(90-C6)) *COS(RADIANS(90-C5)) – the RADIANS functions convert the values into radians form and COS provides the cosine of the values, the cosines for latitude are multiplied then.
    Output – 0.365377540842758
  • COS(RADIANS(D6-D5)) – it provides the cosine value for the longitude difference between two locations.
    Output – 0.716476936499882
  • SIN(RADIANS(90-C6)) *SIN(RADIANS(90-C5)) – calculates the diversion of longitudes from 90 in radians form 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 arccosine 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 Distance Between Two Cities in Excel


2. Using Map API to Calculate Miles between Two Addresses

API stands for Application Programming Interface. Excel uses API to connect with any map like Google Map or Bing Map to collect the data for a place. So, we’ll have to create an API key first to apply this method. That’s quite easy but unfortunately, Google doesn’t provide free API, only Bing Map provides free API. So I’ll show this method using the free API key from Bing Map. Then we’ll make a user-defined function using VBA to use the coordinates and API key.

Steps:

  • First, create an API key from this link. I have created one and placed it in Cell B8.
  • Then press ALT + F11 to open the

Using Map API to Calculate Miles between Two Addresses in Excel

  • After that, click as follows to insert a new module: Insert > Module.

Using Map API to Calculate Miles between Two Addresses in Excel

  • Then keep the following codes in the module-
Option Explicit
Public Function GetMiles(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 ("")
GetMiles = Round(Round(WorksheetFunction.FilterXML(mitHTTP.ResponseText, "//TravelDistance"), 3), 0)
End Function

Later, rounded the result to present without decimal values.

  • Next, go back to your sheet.

Using Map API to Calculate Miles between Two Addresses in Excel

Code Breakdown:

  • Here, I’ve written a customed function GetMiles which takes 3 parameters startlocation, destination, and keyvalue, these will be the position of two addresses and the API value respectively.
  • Within the function, there are several variables; First_Value, Second_Value, Last_value, mitHTTP, mitUrl, of different types to store different values of our interest. Then combined the values (stored within mitUrl) and used several object methods (Open, SetRequestHeader, Send) to calculate through the API.

  • Now have a look, our function named GetMiles is ready to use. It needs three arguments- coordinates for the starting place and destination place, API key.

Using Map API to Calculate Miles between Two Addresses in Excel

  • Later, write the following formula in Cell E10–
=GetMiles(E5,E6,C8)
  • Finally, hit the ENTER button to finish.

Here notice, the output is slightly different from the previous method. Because the previous method used a manual formula.

Read More: How to Calculate Distance Between Two Coordinates in Excel (2 Methods)


Conclusion

I hope the procedures described above will be good enough to calculate distance as miles between two addresses. Feel free to ask any question in the comment section and please give me feedback. Visit ExcelDemy to explore more.


Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo