up vote 1 down vote favorite
share [g+] share [fb]

I'm working with Google Docs and want to create a spreadsheet of the following form:

Point A         Point B           Mileage
Los Angeles     Miami             292100
Palo Alto       San Francisco     90

In the spreadsheet, Mileage is automatically calculated. Is there a function in Google Spreadsheet that can do this?

I am looking for "distance driven", not "as the crow flies".

link|improve this question
As the distance increases, the difference between "driving distance" and "crow flight" will diminish accordingly. In any case they probably won't differ by more than a few percent. – GalacticCowboy May 12 '10 at 0:11
feedback

migrated from superuser.com Jul 1 '11 at 16:57

This question came from our site for computer enthusiasts and power users.

5 Answers

up vote 1 down vote

I do not think there is built in Excel function, or extension, to do this.

You might want to check out this Source Forge page, http://zips.sourceforge.net/

  • It provides a CSV dataset to convert Zip Codes to Lat/Long (center point presumably)
  • It provides Excel code to calculate the "crow flies" distance between two lat/long coordinates. (and includes equivalent code for Java, PHP and Python.

The Excel function is =IF(SIN(Lat1) * SIN(Lat2) + COS(Lat1) * COS(Lat2) * COS(Long1 - Long2) > 1, RadiusofEarth * ACOS(1), RadiusofEarth * ACOS(SIN(Lat1) * SIN(Lat2) + COS(Lat1) * COS(Lat2) * COS(Long1-Long2)))

The page notes that the angles have to be in Radians and that Excel has a degrees to Radians conversion function:

=RADIAN(degrees)

Unless someone else has a slicker answer, this should get you what you want without too much work.

link|improve this answer
yes, but what about distance driven instead of "as the crow flies"? – Rosarch Oct 18 '09 at 16:08
1  
That is considerable more difficult and would require knowledge of all roadways, as is done in Mapquest, Google Earth, Streets&Trips(or MapPoint). I don't know if any of these apps have a suitable API. – Kije Oct 18 '09 at 18:30
... on the other hand, if you were looking for a rough and ready approximation for a fairly limited number of cities, you could look for the driving distance tables that are fairly common in driving maps and atlases. Replicating that style of data in Excel and constructing a lookup to retrieve it for pairs of locations is certainly feasible in Excel. If that appeals, reply and I will could work up a function for it. – Kije Oct 18 '09 at 18:34
feedback
up vote 0 down vote

I am not familiar enough with the APIs to tell you exactly how to do this, but I would think it would be possible using the Google Maps API.

Google Maps, of course, can provide directions from one point to another through their API. It would be complicated, but possible to route the locations from the spreadsheet through the API and get driving directions, which would give you a driving distance between cities.

link|improve this answer
feedback
up vote 0 down vote

You can write the functions on your own which is similar like macro's

http://www.google.com/google-d-s/scripts/yourfirstscript.html

you can find the full document here

PS: Currently It doesn't open for all Google Apps Script

link|improve this answer
feedback
up vote 0 down vote

In Google Spreadsheets:

=importXML("http://mapquestapi.com/directions/v1/route?key=YOUR_KEY_HERE&outFormat=xml&from=" & A2 & "&to=" & B2,"//response/route/distance")

where A2 is the origin and B2 is the destination.

You have to get an API key from mapquest and replace the value YOUR_KEY_HERE with it.

link|improve this answer
feedback
up vote 0 down vote

For a complete Google spreadsheet example, in the form of a spreadsheet template, plus information about the Google Maps API: http://winfred.vankuijk.net/2010/12/calculate-distance-in-google-spreadsheet/

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.