Ever wanted to calculate distance using Excel – between two locations (physical addresses)?
If we know the addresses, we can go to either Google Maps or Bing Maps and type them out to find the distance and travel time. But what if you are building some model (or calculator) and want to find out the distance, travel time, address points (latitude, longitude) and may be even distance matrix (given two sets of points, all distances between them)? We can use the public APIs from Bing maps or Google Maps to get the answer to our spreadsheet.
What you need:
- Free maps API from either Google Maps or Bing Maps
- Excel 2013 or above (we will be using WEBSERVICE() and FILTERXML() functions in Excel)
How to get the API Key from Google Maps or Bing Maps:
The API key process is somewhat technical and can be confusing. Plus for Google Maps API, you need to provide your credit card details (according to Google, you will not be billed automatically though). I made a small video explaining the process. Watch it below (or on our YouTube channel).
Using Excel to calculate distance & travel time between two points – Bing Maps API
As the process for getting Bing Maps API key is easy, let’s assume that is what you have.
Let’s say you have the api key in a cell named bingmaps.key
In this demo, we focus on calculating distance & travel time between one set of points, but you can use the ideas to calculate distance matrix for a range of points. For example, you can calculate travel time between 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. So we will use point lookup API to convert address to geolocation (lat&long). To do this, we call
http://dev.virtualearth.net/REST/v1/Locations?countryRegion=$1&adminDistrict=$2&locality=$3&postalCode=$4&addressLine=$5&maxResults=1&o=xml&key=bingmaps.key
with our address.
Notice all $ symbols? Use SUBSTITUTE to replace them with actual location values.
When you call this URL using WEBSERVICE(), you will get an XML output (as our output parameter is o=xml, if you omit this, you will get json).
Sample output for this looks like below:
<?xml version=”1.0″ encoding=”utf-8″?><Response xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:xsi=”http://www.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 be 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><Location><Name>Phillip St, Johnsonville, Wellington 6037, New Zealand</Name><Point><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 City</AdminDistrict2><CountryRegion>New Zealand</CountryRegion><FormattedAddress>Phillip St, Johnsonville, Wellington 6037, New Zealand</FormattedAddress><Locality>Wellington</Locality><PostalCode>6037</PostalCode></Address><Confidence>High</Confidence><MatchCode>Good</MatchCode><GeocodePoint><Latitude>-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, we need to extract the LAT & LONG values highlighted in blue. We can use FILTERXML() to do that.
Let’s say the output of WEBSERVICE is in cell C21.
We can use FILTERXML() like this:
=FILTERXML(C21,”//Latitude[1]”)
=FILTERXML(C21,”//Longitude[1]”)
This will give us both lat & long values.
How does FILTERXML() work? It takes the XML value in C21 and finds the first Latitude tag (hence [1]) anywhere (hence //)
You can use FILTERXML to test the status code for the response or other interesting bits too.
Step 2: Calculate distance between two geolocations
Once we have lat & long values for both origin and destination, we can call distance lookup API to calculate distance, travel time values.
The distance lookup URL is:
https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=$1&destinations=$2&travelMode=$3&o=xml&key=bingmaps.key
For example, the distance lookup URL for above 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 like:
<?xml version=”1.0″ encoding=”utf-8″?><Response xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:xsi=”http://www.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 be 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>8.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[1]”))
The default output values are in KM for distance and minutes for duration. You can change this to miles, hours etc. too by using extra parameters in the lookup URL. Please 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 named gmaps.key
This API is really easy to use compared to Bing maps (as we need to make just one call).
The request URL is:
https://maps.googleapis.com/maps/api/distancematrix/xml?origins=$1&destinations=$2&mode=$3&key=gmaps.key
For example, let’s lookup the travel time and distance between Microsoft & APPLE offices.
The sample URL is:
https://maps.googleapis.com/maps/api/distancematrix/xml?origins=1,+Infinity+loop,+San+Francisco,+CA&destinations=Redmond,+Seattle,+WA&mode=driving&key=gmaps.key
The response is XML (if you want json, then replace xml with json) like below:
<?xml version=””1.0″” encoding=””UTF-8″”?>
<DistanceMatrixResponse>
<status>OK</status>
<origin_address>Apple Campus, Cupertino, CA 95014, USA</origin_address>
<destination_address>Redmond, WA, USA</destination_address>
<row>
<element>
<status>OK</status>
<duration>
<value>47736</value>
<text>13 hours 16 mins</text>
</duration>
<distance>
<value>1379709</value>
<text>1,380 km</text>
</distance>
</element>
</row>
</DistanceMatrixResponse>
We can FILTERXML this response to extract the important bits like this:
=FILTERXML(C15,”//distance[1]/text”)
=FILTERXML(C15,”//duration[1]/text”)
Download distance calculator template
Click here to download distance, travel time calculator template to see all these formulas in action. You must enter your API key to get it work. Examine the formulas and XML formats to learn more about how these APIs work and how to integrate them to your spreadsheet models.
More 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 section.
79 Responses to “Calculate travel time and distance between two addresses using Excel + Maps API”
Following Google (https://developers.google.com/maps/documentation/javascript/get-api-key), you could give your key:
Get API Key
To use the Maps JavaScript API, you must register your app project on the Google Cloud Platform Console and get a Google API key which you can add to your app.
Would you please?
Thank You so much... It's Really Helpful. We have HTTP Api for Sending SMS and Getting status of the same. With the help of WebService Function, It's become very easy to update the status.
Hi Chandoo!
Your readers may find these useful.
https://dhexcel1.wordpress.com/2017/07/10/getting-the-latest-earthquake-alert-using-the-webservice-and-filterxml-functions-in-excel-by-david-hager/
https://dhexcel1.wordpress.com/2017/09/18/get-latest-storm-information-in-excel-using-only-an-address-and-storm-name/
https://dhexcel1.wordpress.com/2017/09/12/using-excel-to-find-how-far-the-storm-is-from-your-location/
https://dhexcel1.wordpress.com/2017/07/13/lookup-a-bible-verse-in-any-language-using-excel-by-david-hager/
https://dhexcel1.wordpress.com/2017/07/11/excel-getting-the-sunrise-and-sunrise-times-from-an-address-by-david-hager/
Awesome links David.. 🙂
Back in 2012 I attempted to create a Great Circle Calculator with Excel based on examples at ExcelAutomate.
I was able to calculate bearings, distances, and so forth, but never could get them to plot properly.
I would love to see you take your disctance and time calculating capability and tweak it to produce Great Circle plots.
@Trevithick
Plotting circles is quite easy using named formula
If you ask the question in the Chandoo.org Forums,
https://chandoo.org/forum/
also attach a sample file with a drawing of what your after
Hello Hui,
My old file no longer functions except for the calculations due to web services and the like changing over the years.
A good article "Why are Great Circles the Shortest Path?" can be found at https://gisgeography.com/great-circle-geodesic-line-shortest-flight-path/
@Trevithick
Plotting circles and great circles is quite easy using named formula in Excel
If you ask the question in the Chandoo.org Forums,
https://chandoo.org/forum/
also attach a sample file with a drawing of what your after
Is there a way to make this stuff culture aware?
Currently the nice file provided is useless if your decimal separator is a comma.
Using Bing I tried adding "&culture=de" but that didn't help.
How do I change output to miles?
For the Microsoft API insert "&distanceUnit=mi" into the Distance Lookup URL
Good suggestion Chris. I believe Google Maps API too has a parameter to set units for the output values. You can also use Excel's own CONVERT function to do this - =CONVERT(90,"km","mi")
Thought I would add that you can add units=imperial to the url to get miles instead of km.
Dear Chandoo,
This is Awesome, could u please Tweak a little to add the function such as :
1. drop down to calculate time value in any $ range 10 to 20.
2. To calculate road/Bridge Tolls
3. Drop down Cost per mile using range $1 to 3
4. To select one way or round trip cost.
5. To cal calculate weight of package more than 5 lbs @ rate $ 0.10.
This will be a snap for u to tweak, I currently calculate manually using Excel.
Thanks for all the help u provide to excel community
kanu
2156774149.
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? ". Any idea what the problem might be?
Cheers 🙂
@Jonathan... Thanks for the question. Which version of Excel are you using? WEBSERVICE() is a new function and you need at least Excel 2013.
@Chandoo.. I'm using Excel for Mac Version 16.17 180909.
Thanks a lot for this guide, it really helped!
Since it is much easier to get an API Key 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).
even i tried using &avoid=tolls but it doesnt work
Hello Chandoo
In the example you calculate the time as a decimal number. How can ik convert this to the timeformat h:mm:ss
@Donald
Select the cell/s
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
apply
The [] mean that if the number is say 27 hrs it will display as 27 not as 3
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/Resources/Resource/Results/Distance/TravelDistance")
Again, I just get #N/A
Could you explain how it's meant to be structured?
Thanks
Got the answer from the LibreOffice forum.
Apparently, you have to take the XML's "default namespace" into account
FILTERXML(S3,"//*[local-name() = 'TravelDistance']")
That worked for me.
Hello Chandoo
I'm using the Bing Maps API. I've a question about the results of the formulas of coördinates, km and minutes. The calculationsheet gives a traveldistance and travelduration of -1. Also the coördinates aren't right. For example, I've got 532191 instead of 53.2191. Can you explain how this is possible?
Thank you!
Chandoo,
Thank-you for your article, which has been a great 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/element/distance/value") for metres, and then converted to miles.
Best regards,
Tim.
Hi Chandoo,
Thanks for this. I used your google maps sheet to re-create one with columns based on my need. I procured the API key, but when I use it (either on your sheet or the one I created), the status reads REQUEST_DENIED.
This is possibly nothing to do with the sheet itself, but any suggestions on how I can rectify this?
I get a value error not sure why I have an API key and when I review my console it show the errors so it reaching to the API Any ideas how to fix?
Go to credentials and request new key then copy and paste, it worked for me
Hi Chandoo,
I found your video very helpful. Thank you for providing the file to download.
I just obtained a bing maps api key. Do you know how address distances I can calculate under the the free tier?
Thank you for your help.
Hi,
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?
Thank you for your feedback.
Hi Chandoo!
When I change travel mode from Driving to Transit, I do not see a change in either Distance or Travel Time.
Is there something else I need to do?
Hello Chandoo
Can you help me code up a module for the bing map api so that it will be easer to get the distance of 2 address if you have a them in a list. i use to use the google api however its not free anymore.
Thank you so much for this!!
Hi Chandu, thanks for this - its very useful.
When I send this sheet to others its not working. The sheet has my API key, are there any restrictions on my API key if its been used by anyone other than me?
The cell returns a value error.
Same problem with my distance sheet
Hi Chandu
Is there a way to choose a specific time or day of travel? Or even get an average travel time across a range (e.g. between 08:00-09:00 on a weekday in February)?
Thanks in advance
Wayne
Wondering if there's a way to get "Depart at" time in the formulas so traffic at, say, 7AM could be considered
Hi Chando,
I tried your distance calculator but I can only use it on my computer, I need to share it interactive so other people can change addresses to calculate distances, I convert it to google sheets but google sheets does not have full funcionality, tried One drive it also did not work, please let me know how can I share it without liising functionality
Hi Rukhsana...
When you share the files online, your users will also use the same key. If you are using paid keys, then shared files can increase the uses rapidly and you will be billed accordingly. I think Excel online based sharing should work as long as you are ok with the above.
Hello. The function Web Service () does not work correctly. When I execute the link everything is ok, but the cells Response and Distance can not show the results.
@Gilson, can you test the webservice call by pasting the URL in browser? Does it give a result? If not, you may need to find the correct URL + parameters.
Yes, the URL is ok when testing in browser. Only the result showed in the ceel of Excel is #VALUE
@Gibson, were you able to solve this? I am having the same issue and from what I can tell, it's not a problem with the url. I am unable to use WEBSERVICE for any url.
I have an problem in Bing maps sheet.
Distance is -1,00 km and Travel Time is also -1,00 mins how can I get the correct values?
This is because of your Settings to "Point" and "Comma" in Excel (or your operating system).
In Excel, go to "File" --> "Options" --> "Advanced" (don't know the english title, sorry) --> deactivate option "use delimiter from operating system", change decimal to ".", thausend to "," --> "OK"
afterwards it will work.
Great Post Chandoo!
Do you have a distance calculator for multiple addresses?
Hello
I tried to use the Bing template to calculate distances and time of travel exactly as it was shown in this page but for some reason, it is not working !!!
I have my own key in Bing.
Do you have an idea if I am missing something?
Thank you
Did they move the urls? I get #VALUE! no matter what I do...
I'm wondering what the travel time is calculating - is this current traffic, average traffic, or no traffic?
I am having the same "-1.00" distance and travel time issue, it seems to work for "Driving" Mode but shows -1.00 when I change the mode to Walking (which is what I am trying to use this for). any thoughts?
Chandoo ,
1) I created a Distance Matrix with all the locations you want to visit and the intersection give distance and
2)another table where intersection gives the time taken .
3)Then 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 time of 5 mins.
3c) Applied constraints that some stops wants the visit time in between say 3pm- 4 pm or 2pm - 6 pm .. like that
3d) Solve it and it gave the best TSP solution .
Succesfully ran upto 20 stops( may take 2 mins to run) , beyond that it will take too much time .
Chandoo -
I'm using the Bing maps calculator and both Travel modes (walking & driving) are calculating but when selecting the Travel Mode as 'transit', it only 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' but not sure how to proceed with adding the arrival time field into excel and editing the required URL parameters. Thoughts?
Sorry - I am NOT using 'transit' but rather 'driving' as the Travel Mode. I need to add 'arrive by time' to the Lookup URLs for the 'driving' query but not sure how to proceed with adding the 'arrive by time' field into excel and editing/adding the syntax into the Lookup URLs. Can you assist?
Hi Chandoo,
Thanks 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.
Thanks
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 different time of the day. Thanks
This is the best set of instructions I've found - thank you so much.
I don't know which restrictions to apply to my API key, as I'm using it from Excel not an online app etc.
Does anyone know how I can let another team member use my API key on our shared drive - it doesn't let 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 work)?
Hi Chandoo
Great instructions, easy to use.
The only problem I had was I kept getting API not authorised error message when following the link.
I discovered on google, you have to enable the API for use with Distance Matrix before it works.
I have a Mac : ( which means web service doesn't work. So even after I figured out the API issue, I will never be able to see the beautiful results displayed in excel.
So sad : (
Thanks anyway
Have a great day.
Nik
Hi sir can u please tell me how can we calculate the journey speed between two places with in the city using the google map APIs
Hi, Thank you for these instructions. I have been using your model for some time, but suddenly, the results for different modes of transport (transit/walking/driving) are the same. It seems whichever mode of transport is selected, the result is the driving travel time. I checked Microsoft documentation as well, but I could not find why it does not work as it should. Any idea, please?
Great script. I was able to use the bing maps API. How do you add a waypoint to the calculation? Can this script be modified to do a list of pickup and dropoff points?
Hi Chandoo, I have been using your script for some time and it calculated distances and times correctly. Today, however it returned -1.00 distance. I have used my lats and longs in your sheet as well and get the same result. What is causing this?
I have the same problem. I have a value "-1" for Distance and Travel Time. What does it mean?
The statuscodevalue = 200. What does it mean?
I have a valid api key.
I ame working with "Office 365"
Hi, Chandoo:
Great website and samples to help us to learn and leap forward.Great job and appreciate it.
I recently download your Distance calculator template and it does work for the first day, and then stops working. I have checked every detail and even re-downloaded your template by just input my Bingmaps Key. It seems like that the API stopped working. Can you help to verify it?
Thank you so much
Steve from Canada
Hello Chandoo,
Thanks for providing your excel files.
I would like to know if there is any way to calculate travel duration in past time in Bing API. For example, for 2020-03-01.
Best,
Masoud
Mouth literally agape at how awesome this is you put this together. Literally thought 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 thank you so much!
Hi,
I am trying to use your template but only get #VALUE" errors in the results fields.
Already enabled all options in Trust Center to allow links etc.
Pasting the url directly into the browser shows me the correct xml answer
Any suggestion?
Thx
Hi,
I also get the #VALUE! error too with the Bing Maps API Key. In the browser, I get the correct XML answer.
Thanks for your help.
This is amazing. Thank you @Chandoo.
Is there a simple way to add a 3rd point on the route? Instead of start and end. I was looking for a way to have Start, waypoint, end.
Any assistance would be greatly appreciated.
Thank you and keep up the fantastic work.
Gray
This is great, thank you. For the Google sheet, is there a way to get flight times in addition to the pre-defined choices of "driving, walking, bicycling & transit"? I added "flight" (since that seems to be a valid Google API designation) to the data validation dropdown list, but it returns the same info as driving. Also, can you think of a way to enter multiple locations in the spreadsheet and then calculate distances from one location to all of them? I am trying to calculate which one of my employees is closest to a client location that I enter. Thanks.
How can i have the output of Distance to be numeral only so without units. As i will use this number in another formula. I have used the Google APIs option you've shown.
Thanks you.
You can use substitute function to remove any letters like KM or Mi from the output and then apply VALUE() function to convert the thing to numbers.
Hello,
this file was working perfectly... until some weeks ago.
I'm using the google maps solution. Clicking on the URL, I got following message:
The provided API key is invalid.
I tried to create a new key, but with the same result. Someone has a solution for this? Or is this still working for others?
thanks
Great calculator!
Is it possible somehow to calculate the distance for "flight" mode ?
Hi Chandoo,
Thanks very much for all your help via your YouTube channel!
I need to use the exact model that you provided here (https://chandoo.org/wp/distance-between-places-excel-maps-api/), but must also have a stopover (waypoint) between the origin and destination.
How can I please adjust the formulas to fit in a stopover in the middle?
I've edited your formula like so:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(gmaps.distance.url;"$1";SUBSTITUTE('Matrix Interface'!B16&", "&'Matrix Interface'!C16;" ";"+"));"$2";SUBSTITUTE('Matrix Interface'!D16&", "&'Matrix Interface'!E16;" ";"+"));"$4";SUBSTITUTE('Matrix Interface'!D18&", "&'Matrix Interface'!E18;" ";"+"));"$3";gmaps.travel.mode);"$k";gmaps.key)
This is the resultant URL:
https://maps.googleapis.com/maps/api/distancematrix/xml?origins=4852,+Schweiz&stops=1201,+Schweiz&destinations=4624,+Schweiz&mode=driving&key=AIzaSyADazc10exgWt_bkwVlLetURVJdiH_2MBY
However, the waypoint doesn't come up in the response:
"
OK
4852 Rothrist, Switzerland
4624 Härkingen, Switzerland
OK
839
14 mins
9981
10.0 km
"
Hope to hear back from you.
Best regards,
Nigel
I used this template last week and it worked perfectly. I went to put the finishing formatting touches on it, and I'm getting #VALUE for time and distance. I tried creating a new API key to see if that was the issue and that didn't fix it. I went back to your original template and I'm having the same issue. Do you know what it could be?
Hi Chandoo,
Concerning the Download distance calculator template, i have the following questions and i wish you will answer them in this order:
1. Where exactly am I supposed to insert my bing maps API key?
2. What is the earliest version of microsoft excel that this formula works in?
3. Does it generate the distance matrix on its own or do i have to do anything else to generate the entire distance matrix?
4. If I have to generate the driving distance matrix myself, how do i do that?
Thank you helping.
Hi there,
for some reason the file works great iwith US zip codes, unless they start with a "0", like NJ or New England. The the calculator spits out zeros for distance and driving time, no matter what the zip codes are.
I formatted the zip codes a s zip codes in Excel, not as text. This way they should be recognized as number and still show the "0" at the beginning.
Any idea what the problem is?
Ludger