How To Solve Lowest Cost Shipping Distance In Excel Solver

Excel Howtos

Calculate travel time and distance betwixt ii addresses using Excel + Maps API

Terminal updated on July 19, 2018

Chandoo

Ever wanted to calculate distance using Excel – between two locations (physical addresses)?

If nosotros know the addresses, we can go to either Google Maps or Bing Maps and type them out to discover the distance and travel time. But what if y'all are edifice some model (or figurer) and want to find out the distance, travel fourth dimension, address points (latitude, longitude) and may exist even distance matrix (given 2 sets of points, all distances between them)? We can utilize the public APIs from Bing maps or Google Maps to get the reply to our spreadsheet.

What you demand:

Gratuitous maps API from either Google Maps or Bing Maps
Excel 2013 or to a higher place (we volition be using WEBSERVICE() and FILTERXML() functions in Excel)

How to go the API Key from Google Maps or Bing Maps:

The API primal process is somewhat technical and can be disruptive. Plus for Google Maps API, you lot demand to provide your credit card details (according to Google, you will not be billed automatically though). I fabricated a small video explaining the process. Watch it below (or on our YouTube channel).

Using Excel to summate altitude & travel fourth dimension betwixt ii points – Bing Maps API

Every bit the process for getting Bing Maps API key is easy, let's assume that is what you have.

Let'southward say you take the api key in a cell namedbingmaps.primal

In this demo, we focus on calculating distance & travel time between one prepare of points,but you can utilize the ideas to calculate distance matrix for a range of points.For example, yous tin can calculate travel fourth dimension betwixt all your warehouses and customer locations easily.

Start by creating a range of cells to capture origin & destination addresses. For Bing maps API, we need address to be broken in to below pieces.

Step 1: Fetch Latitude and Longitude for the addresses

Before calculating the distance, we need to know where on earth our addresses are. And so we will utilize indicate lookup API to convert address to geolocation (lat&long). To practice this, nosotros call

http://dev.virtualearth.net/Residue/v1/Locations?countryRegion=$1&adminDistrict=$2&locality=$3&postalCode=$4&addressLine=$5&maxResults=1&o= xml &primal=bingmaps.key

with our accost.

Notice all $ symbols? Utilise SUBSTITUTE to replace them with actual location values.

When you phone call this URL using WEBSERVICE(), y'all volition go an XML output (as our output parameter is o=xml, if you omit this, you volition get json).

Sample output for this looks similar below:

<?xml version="one.0″ encoding="utf-viii″?><Response xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://world wide web.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/search/local/ws/balance/v1″><Copyright>Copyright © 2018 Microsoft and its suppliers. All rights reserved. This API cannot be accessed and the content and any results may not exist used, reproduced or transmitted in whatever manner without express written permission from Microsoft Corporation.</Copyright><BrandLogoUri>http://dev.virtualearth.cyberspace/Branding/logo_powered_by.png</BrandLogoUri><StatusCode>200</StatusCode><StatusDescription>OK</StatusDescription><AuthenticationResultCode>ValidCredentials</AuthenticationResultCode><TraceId>_REMOVED_</TraceId><ResourceSets><ResourceSet><EstimatedTotal>ane</EstimatedTotal><Resource><Location><Name>Phillip St, Johnsonville, Wellington 6037, New Zealand</Name><Indicate> <Latitude>-41.22292</Latitude><Longitude>174.80164</Longitude> </Point><BoundingBox><SouthLatitude>-41.2241799</SouthLatitude><WestLongitude>174.80136</WestLongitude><NorthLatitude>-41.22166</NorthLatitude><EastLongitude>174.80196</EastLongitude></BoundingBox><EntityType>RoadBlock</EntityType><Address><AddressLine>Phillip St</AddressLine><AdminDistrict>Wellington</AdminDistrict><AdminDistrict2>Wellington Metropolis</AdminDistrict2><CountryRegion>New Zealand</CountryRegion><FormattedAddress>Phillip St, Johnsonville, Wellington 6037, New Zealand</FormattedAddress><Locality>Wellington</Locality><PostalCode>6037</PostalCode></Address><Conviction>High</Conviction><MatchCode>Good</MatchCode><GeocodePoint><Breadth>-41.22292</Latitude><Longitude>174.80164</Longitude><CalculationMethod>Interpolation</CalculationMethod><UsageType>Display</UsageType></GeocodePoint><GeocodePoint><Latitude>-41.22292</Latitude><Longitude>174.80164</Longitude><CalculationMethod>Interpolation</CalculationMethod><UsageType>Route</UsageType></GeocodePoint></Location></Resources></ResourceSet></ResourceSets></Response>

From this XML, nosotros need to extract the LAT & LONG values highlighted in bluish. We can use FILTERXML() to do that.

Let's say the output of WEBSERVICE is in prison cell C21.

We tin can use FILTERXML() similar this:

=FILTERXML(C21,"//Latitude[1]")

=FILTERXML(C21,"//Longitude[1]")

This will give us both lat & long values.

How does FILTERXML() work?Information technology takes the XML value in C21 and finds the kickoff Latitude tag (hence [one]) anywhere (hence //)

You tin utilise FILTERXML to test the condition lawmaking for the response or other interesting bits too.

Stride 2: Summate distance between ii geolocations

Once we have lat & long values for both origin and destination, we tin can call distance lookup API to calculate distance, travel time values.

The altitude lookup URL is:

https://dev.virtualearth.net/Residual/v1/Routes/DistanceMatrix?origins=$one&destinations=$ii&travelMode=$3&o=xml&key=bingmaps.key

For example, the distance lookup URL for in a higher place addresses is:

https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=- 41.22292,174.80164 &destinations= -41.27868,174.77506 &travelMode= driving &o=xml&key=$k

The output for this is an XML that looks similar:

<?xml version="one.0″ encoding="utf-8″?><Response xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://world wide web.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/search/local/ws/rest/v1″><Copyright>Copyright © 2018 Microsoft and its suppliers. All rights reserved. This API cannot exist accessed and the content and any results may not be used, reproduced or transmitted in any manner without express written permission from Microsoft Corporation.</Copyright><BrandLogoUri>http://dev.virtualearth.net/Branding/logo_powered_by.png</BrandLogoUri><StatusCode>200</StatusCode><StatusDescription>OK</StatusDescription><AuthenticationResultCode>ValidCredentials</AuthenticationResultCode><TraceId>_REMOVED_</TraceId><ResourceSets><ResourceSet><EstimatedTotal>1</EstimatedTotal><Resources><Resource xsi:type="DistanceMatrix"><ErrorMessage>Request accepted.</ErrorMessage><Origins><Coordinate><Latitude>-41.22292</Latitude><Longitude>174.80164</Longitude></Coordinate></Origins><Destinations><Coordinate><Latitude>-41.27868</Latitude><Longitude>174.77506</Longitude></Coordinate></Destinations><Results><Distance><DepartureTime xsi:nil="true" /><OriginIndex>0</OriginIndex><DestinationIndex>0</DestinationIndex> <TravelDistance>eight.96955555555556</TravelDistance> <TravelDuration>7.29166666666667</TravelDuration> <TotalWalkDuration>0</TotalWalkDuration></Distance></Results></Resource></Resources></ResourceSet></ResourceSets></Response>

Again, we can use FILTERXML() to extract the relevant bits (=FILTERXML(C32,"//TravelDistance[1]") and =FILTERXML(C32,"//TravelDuration[i]"))

The default output values are in KM for distance and minutes for duration. Yous can change this to miles, hours etc. too by using extra parameters in the lookup URL. Delight read the Bing maps developer documentation for more.

Distance & travel time in Excel – Google Maps API

Let's say your Google Maps API key is in a cell namedgmaps.key

This API is really easy to employ compared to Bing maps (every bit we need to make but one call).

The request URL is:

https://maps.googleapis.com/maps/api/distancematrix/ xml ?origins=$1&destinations=$two&style=$3&central=gmaps.central

For example, let's lookup the travel fourth dimension and distance betwixt Microsoft & APPLE offices.

The sample URL is:

https://maps.googleapis.com/maps/api/distancematrix/xml?origins=ane,+Infinity+loop,+San+Francisco,+CA&destinations=Redmond,+Seattle,+WA&style=driving&key=gmaps.key

The response is XML (if you want json, then supersede xml with json) like below:

<?xml version=""1.0″" encoding=""UTF-8″"?>
<DistanceMatrixResponse>
<condition>OK</status>
<origin_address>Apple Campus, Cupertino, CA 95014, The states</origin_address>
<destination_address>Redmond, WA, U.s.</destination_address>
<row>
<element>
<condition>OK</status>
<duration>
<value>47736</value>
<text> 13 hours 16 mins </text>
</duration>
<altitude>
<value>1379709</value>
<text> 1,380 km </text>
</distance>
</element>
</row>
</DistanceMatrixResponse>

Nosotros tin can FILTERXML this response to extract the important bits like this:

=FILTERXML(C15,"//altitude[1]/text")

=FILTERXML(C15,"//duration[1]/text")

Download distance calculator template

Click here to download distance, travel time calculator template to run across all these formulas in action. Yous must enter your API fundamental to become it work. Examine the formulas and XML formats to acquire more near how these APIs work and how to integrate them to your spreadsheet models.

More than examples of WEBSERVICE():

Finding nearest zip codes using webservice() and filterxml()
Integrating WEBSERVICE() with trip planner spreadsheet [Microsoft blog]

Made something cool with WEBSERVICE()?

Did you make something cool and fun using WEBSERVICE() and FILTERXML()? Please share the ideas and tips in comments department.

Share this tip with your colleagues

Become Complimentary Excel + Ability BI Tips

Simple, fun and useful emails, one time per week.

Learn & be awesome.

69 Comments
Ask a question or say something...
Tagged under advanced excel, api, downloads, filterxml(), google maps, Microsoft Excel Formulas, videos, webservice(), xpath
Category: Excel Howtos

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make yous awesome in Excel & Power BI. I practise this by sharing videos, tips, examples and downloads on this website. There are more than i,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my story • FREE Excel tips book

Excel School made me nifty at work.

     5/five

Related Tips

69 Responses to "Calculate travel time and distance between two addresses using Excel + Maps API"

Amit Jalan says:

Cheers and then much... It'south Really Helpful. Nosotros accept HTTP Api for Sending SMS and Getting condition of the same. With the help of WebService Role, Information technology's become very easy to update the status.
Trevithick says:

Back in 2012 I attempted to create a Nifty Circle Calculator with Excel based on examples at ExcelAutomate.

I was able to calculate bearings, distances, and and then forth, but never could get them to plot properly.

I would beloved to see you lot take your disctance and time calculating adequacy and tweak it to produce Groovy Circle plots.
- @Trevithick
  
  Plotting circles is quite easy using named formula
  
  If y'all ask the question in the Chandoo.org Forums,
  https://chandoo.org/forum/
  also attach a sample file with a cartoon of what your subsequently
  - - @Trevithick
      
      Plotting circles and great circles is quite piece of cake using named formula in Excel
      
      If you lot ask the question in the Chandoo.org Forums,
      https://chandoo.org/forum/
      also attach a sample file with a cartoon of what your subsequently
FrankT says:

Is at that place a way to make this stuff civilisation aware?

Currently the nice file provided is useless if your decimal separator is a comma.

Using Bing I tried adding "&civilization=de" merely that didn't help.
How exercise I modify output to miles?
- Chris Hanson says:
  
  For the Microsoft API insert "&distanceUnit=mi" into the Distance Lookup URL
  - Practiced proposition Chris. I believe Google Maps API too has a parameter to set units for the output values. Y'all can also utilize Excel'south ain Convert function to exercise this - =Convert(xc,"km","mi")
    - Idea I would add that y'all can add together units=royal to the url to go miles instead of km.
Kanyalal Bhatia says:

Honey Chandoo,
This is Awesome, could u please Tweak a little to add the function such as :
1. drop down to calculate time value in whatsoever $ range x to twenty.
ii. To calculate route/Bridge Tolls
iii. Driblet downwards Cost per mile using range $one to three
4. To select one mode or circular trip cost.
5. To cal summate weight of parcel more than 5 lbs @ rate $ 0.x.
This will exist a snap for u to tweak, I currently calculate manually using Excel.
Cheers for all the assist u provide to excel customs
kanu
2156774149.
Jonathan says:

I've generated a Bing key and inserted it at the right place but the spreadsheet doesn't seem to work for me:Most fields show " #Name? ". Whatsoever thought what the problem might be?

Cheers 🙂
- @Jonathan... Thanks for the question. Which version of Excel are y'all using? WEBSERVICE() is a new part and you lot need at least Excel 2013.
  - Jonathan says:
    
    @Chandoo.. I'thousand using Excel for Mac Version 16.17 180909.
Hans says:

Thanks a lot for this guide, information technology really helped!

Since it is much easier to get an API Cardinal for Bing, i am using this alternative. Is there way to calculate the distance and time between two coordinate avoiding tolls? (i used "&avoid=tolls&" in the URL, but it is not working).
- pk says:
  
  even i tried using &avoid=tolls but it doesnt work
Donald Claessens says:

Hello Chandoo

In the example yous calculate the time equally a decimal number. How can ik catechumen this to the timeformat h:mm:ss
- @Donald
  
  Select the cell/south
  Right click and select format Cells or Press Ctrl+1
  Select the Number Format tab
  Select the Custom Format Tab
  In the Custom Format Dialog enter h:mm:ss or [h]:mm:ss
  use
  
  The [] mean that if the number is say 27 hrs information technology will brandish as 27 non as iii
Fitch says:

I use LibreOffice Calc, and having no luck in getting the travel distance with a filterxml.
=FILTERXML(C32,"//TravelDistance[1]")
brings up #N/A.

I tried:
FILTERXML(C32,"//ResourceSets/ResourceSet/Resource/Resource/Results/Distance/TravelDistance")
Again, I merely become #Northward/A

Could yous explicate how it'south meant to be structured?
Thanks
- Fitch says:
  
  Got the answer from the LibreOffice forum.
  Obviously, yous have to take the XML's "default namespace" into account
  FILTERXML(S3,"//*[local-name() = 'TravelDistance']")
  That worked for me.
Anne says:

How-do-you-do Chandoo

I'm using the Bing Maps API. I've a question virtually the results of the formulas of coördinates, km and minutes. The calculationsheet gives a traveldistance and travelduration of -one. Likewise the coördinates aren't right. For example, I've got 532191 instead of 53.2191. Can you explain how this is possible?

Give thanks yous!
Chandoo,

Thank-yous for your article, which has been a cracking help.

It is frustrating that these solutions do not work on Excel for Mac, but I have Parallels and Excel for Windows running.

Also to say that to get distance out, I used

=filterXML(cell,"//row/chemical element/distance/value") for metres, and then converted to miles.

Best regards,

Tim.
Varun Raj Singh says:

Hi Chandoo,

Cheers for this. I used your google maps sheet to copy one with columns based on my need. I procured the API fundamental, just when I employ it (either on your sheet or the one I created), the status reads REQUEST_DENIED.

This is perchance nothing to do with the sheet itself, just any suggestions on how I can rectify this?
Tanner says:

I go a value error not sure why I have an API key and when I review my console information technology bear witness the errors so it reaching to the API Any ideas how to set?
- Rukhsana says:
  
  Go to credentials and request new primal so copy and paste, it worked for me
Zaki says:

Hello Chandoo,

I institute your video very helpful. Cheers for providing the file to download.

I just obtained a bing maps api key. Do you know how accost distances I can summate under the the gratis tier?

Thank you for your help.
Axelle says:

Hullo,

I got the google API as you explained.
When I copy this into the sheet, it doesn't work.
status = REQUEST_DENIED

Am I doing something wrong?
Cheers for your feedback.
Varun Raj Singh says:

Hi Chandoo!

When I change travel fashion from Driving to Transit, I do not run across a change in either Distance or Travel Fourth dimension.

Is there something else I demand to practice?
Allen says:

Hello Chandoo

Tin you help me code up a module for the bing map api so that it will exist easer to get the distance of ii address if yous have a them in a list. i employ to apply the google api withal its non costless anymore.
Jeanine Holtmann says:

Thank you then much for this!!
Aswin Iruppayil Raj says:

Hi Chandu, cheers for this - its very useful.

When I ship this sheet to others its not working. The sheet has my API fundamental, are there any restrictions on my API key if its been used by anyone other than me?

The prison cell returns a value mistake.
- Rukhsana Tanwir says:
  
  Same problem with my distance canvas
Wayne Garside says:

Hi Chandu

Is there a style to choose a specific time or day of travel? Or even get an average travel time across a range (e.thou. between 08:00-09:00 on a weekday in February)?

Thank you in advance

Wayne
Marc Stelzer says:

Wondering if there's a mode to get "Depart at" time in the formulas and so traffic at, say, 7AM could be considered
Rukhsana Tanwur says:

Hi Chando,
I tried your distance calculator but I can simply utilise it on my computer, I demand to share information technology interactive then other people can change addresses to calculate distances, I catechumen it to google sheets simply google sheets does non take full funcionality, tried One drive it also did not work, please let me know how can I share information technology without liising functionality
- Hi Rukhsana...
  
  When you share the files online, your users will as well use the same central. If you lot are using paid keys, then shared files can increment the uses rapidly and y'all volition exist billed appropriately. I think Excel online based sharing should work as long as yous are ok with the above.
Hello. The part Web Service () does not work correctly. When I execute the link everything is ok, merely the cells Response and Distance can not testify the results.
- @Gilson, tin you lot test the webservice phone call past pasting the URL in browser? Does it give a result? If not, y'all may demand to find the right URL + parameters.
  - Yeah, the URL is ok when testing in browser. Only the upshot showed in the ceel of Excel is #VALUE
    - Josh says:
      
      @Gibson, were you able to solve this? I am having the same issue and from what I can tell, it'south not a trouble with the url. I am unable to use WEBSERVICE for whatever url.
Camilla says:

I take an problem in Bing maps sail.

Distance is -1,00 km and Travel Fourth dimension is besides -1,00 mins how can I get the right values?
- Florian R. says:
  
  This is because of your Settings to "Bespeak" and "Comma" in Excel (or your operating organization).
  
  In Excel, go to "File" --> "Options" --> "Advanced" (don't know the english title, sad) --> deactivate selection "use delimiter from operating arrangement", modify decimal to ".", thausend to "," --> "OK"
  
  afterward information technology will work.
Mwanangu says:

Great Mail service Chandoo!
Do y'all accept a distance estimator for multiple addresses?
Mourad B says:

Hello
I tried to employ the Bing template to calculate distances and time of travel exactly every bit it was shown in this folio merely for some reason, information technology is not working !!!
I have my own key in Bing.

Do you have an idea if I am missing something?

Thank yous
James says:

Did they move the urls? I get #VALUE! no matter what I practise...
Haley says:

I'thousand wondering what the travel fourth dimension is calculating - is this current traffic, average traffic, or no traffic?
macutan says:

I am having the same "-ane.00" altitude and travel time effect, it seems to piece of work for "Driving" Mode only shows -ane.00 when I change the mode to Walking (which is what I am trying to use this for). any thoughts?
Majush says:

Chandoo ,
i) I created a Altitude Matrix with all the locations you want to visit and the intersection give altitude and
2)some other tabular array where intersection gives the time taken .
3)So using excel Solver ,
3a)using this info, I created an objective function to minimize the distance travelled for roundtrip
3b)calculated the exact time the vehicle reaches each stop and standard wait fourth dimension of 5 mins.
3c) Applied constraints that some stops wants the visit fourth dimension in between say 3pm- 4 pm or 2pm - six pm .. like that
3d) Solve it and it gave the all-time TSP solution .
Succesfully ran upto twenty stops( may have 2 mins to run) , beyond that it will take too much time .
Wojo says:

Chandoo -
I'1000 using the Bing maps calculator and both Travel modes (walking & driving) are calculating but when selecting the Travel Style as 'transit', it simply calculates as '0'. I am wondering if the URL query is missing some required values. I need to add 'arrival time' as a parameter which is a required parameter when using 'transit' simply not sure how to go on with adding the arrival time field into excel and editing the required URL parameters. Thoughts?
- Wojo says:
  
  Sorry - I am Not using 'transit' but rather 'driving' every bit the Travel Mode. I need to add 'arrive by time' to the Lookup URLs for the 'driving' query only non sure how to proceed with calculation the 'arrive past fourth dimension' field into excel and editing/adding the syntax into the Lookup URLs. Can yous help?
Ipshita says:

Hi Chandoo,

Cheers a lot for the blog. This has been very helpful. I am using the Bing maps api. However, i wanted to know if there is any way to find the shortest distance between two coordinates in case of multiple routes.

Thank you
Dileep says:

Hi Chandoo, thanks for the video. How can I specify the departure time (past) in the excel? I wanted to compare travel time between two points for two unlike time of the mean solar day. Thanks
Emma Littlewood says:

This is the best set of instructions I've found - thanks then much.
I don't know which restrictions to apply to my API key, as I'chiliad using it from Excel not an online app etc.
Does anyone know how I can allow another team fellow member employ my API key on our shared drive - it doesn't permit him use it, just says #VALUE.

THANKS!
- Hi Emma... Thank you. Can you check and confirm your colleague has access to Google API website and using Excel 2013 or above (which is needed for this to piece of work)?
Nikolas Jones says:

Hi Chandoo

Great instructions, like shooting fish in a barrel to use.

The only problem I had was I kept getting API non authorised error message when following the link.

I discovered on google, you have to enable the API for apply with Altitude Matrix before it works.

I have a Mac : ( which means spider web service doesn't work. So even after I figured out the API issue, I will never be able to see the cute results displayed in excel.

So lamentable : (

Thanks anyhow

Have a neat day.
Nik
Sandeep kumar says:

Hello sir tin can u please tell me how can we calculate the journeying speed between two places with in the city using the google map APIs
Marie says:

Hi, Give thanks yous for these instructions. I have been using your model for some time, only all of a sudden, the results for dissimilar modes of transport (transit/walking/driving) are the aforementioned. It seems whichever mode of ship is selected, the result is the driving travel fourth dimension. I checked Microsoft documentation as well, simply I could not find why information technology does non work every bit it should. Whatsoever thought, please?
Great script. I was able to use the bing maps API. How practise you lot add together a waypoint to the calculation? Tin this script be modified to practise a listing of pickup and dropoff points?
Wessel Lourens says:

Hi Chandoo, I have been using your script for some time and information technology calculated distances and times correctly. Today, however it returned -1.00 altitude. I have used my lats and longs in your sheet equally well and get the aforementioned issue. What is causing this?
- I have the aforementioned trouble. I have a value "-1" for Altitude and Travel Fourth dimension. What does it hateful?
  
  The statuscodevalue = 200. What does it mean?
  
  I have a valid api key.
  - Cladon says:
    
    I ame working with "Part 365"
Steve says:

How-do-you-do, Chandoo:

Great website and samples to assist us to learn and leap forwards.Dandy job and appreciate information technology.

I recently download your Distance calculator template and it does work for the first day, and then stops working. I have checked every particular and even re-downloaded your template by just input my Bingmaps Key. Information technology seems like that the API stopped working. Tin you help to verify it?

Thank you lot so much

Steve from Canada
Masoud says:

Howdy Chandoo,

Thanks for providing your excel files.

I would like to know if at that place is any mode to summate travel duration in past fourth dimension in Bing API. For example, for 2020-03-01.

Best,
Masoud
Mike says:

Rima oris literally agape at how awesome this is you put this together. Literally idea I had 0 chance at finding a way to do this through Excel - though i did luck out we had existing API keys! Amazing and give thanks yous so much!
Martin says:

Howdy,

I am trying to use your template but merely become #VALUE" errors in the results fields.
Already enabled all options in Trust Centre to permit links etc.

Pasting the url directly into the browser shows me the correct xml answer

Any proffer?

Thx
Gray Wolf says:

This is amazing. Thanks @Chandoo.
Is there a elementary way to add together a tertiary point on the route? Instead of start and stop. I was looking for a way to have Start, waypoint, terminate.
Any assistance would be greatly appreciated.
Cheers and keep upwardly the fantastic piece of work.
Grayness