Method 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. Let’s follow the instructions below to calculate the distance between two addresses in miles.
Steps:
- Activate Cell D8.
- Add the following formula.
=ACOS(COS(RADIANS(90-C6)) *COS(RADIANS(90-C5)) +SIN(RADIANS(90-C6)) *SIN(RADIANS(90-C5)) *COS(RADIANS(D6-D5))) *3959- Press the ENTER button to get the distance in the Miles unit.
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
Method 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. We will have to create an API key first to apply this method.
Steps:
- Create an API key from this link. I have created one and placed it in Cell B8.
- Press ALT + F11 to open the Developer tab.
- Click Insert > Module.
- Enter the following code 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 FunctionRounded the result to present without decimal values.
- Go back to your sheet.
Code Breakdown:
- I have 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.
- The function named GetMiles is ready. It needs three arguments- coordinates for the starting place and destination place, API key.
- Enter the following formula in Cell E10–
=GetMiles(E5,E6,C8)- Press ENTER to finish.
Download Practice Workbook
Hello Sourov! Question– when i tried to use the formula, it did calculate a number but it was off by at least 50 miles when compared to google maps or bing– does this calculate the distance between point A to point B in a straight line or does it go off roads/highways/etc? Sorry if this is a dumb question, I’m still learning.
Hello Faye,
The formula provided calculates the straight-line (great-circle) distance between two points, not accounting for roads, highways, or specific travel routes. This is why you notice a discrepancy when comparing it to Google Maps or Bing, which calculate driving or walking distances along actual travel paths.
The straight-line distance is the shortest path over the earth’s surface, which does not reflect the true travel distance that might involve various roads and pathways.
Thanks for commenting and asking questions! It’s great that you’re diving into the details and eager to learn. No worries, it’s not a dumb question at all! Learning these differences is part of the process. Keep up the good work!
Regards
ExcelDemy