4

EDIT: Im using Exchel Online Sheets

This is what i have in a column

=importXML("http://maps.googleapis.com/maps/api/geocode/json?address"&"=A4,A5&key=MYKEYISHERE--9-W1GIAHcQb9O6fm0";"B6,B7")

This may be hard to read so let me translate. It should take the latitude from A4 and the longitude from A5 and calculate the distance to B6, B7

The first problem that im seeing is that here /geocode/json?it says JSON even tho I'm importing an XML, so I'm not sure how exactly this works, or how to work around it.

Thank you in advance

Robertme
  • 341
  • 2
  • 4
  • 16
  • Have a look here: https://analystcave.com/excel-calculate-distances-between-addresses/#Calculate_distance_between_two_coordinates – JvdV Jun 12 '18 at 06:38
  • Im using google sheets, there is do developer tab. :( – Robertme Jun 12 '18 at 06:42
  • I would recommend to change your titel and question a bit then. It askes to use geo-coordinates in Excel. – JvdV Jun 12 '18 at 06:43
  • Hey, i tried using it with excel, do you have any experience with it ? I keep getting some errors. – Robertme Jun 12 '18 at 08:23

3 Answers3

5

Robertme,

A quick google search found this way to do the required in Google Sheets: Calculate Lat/Lon Distance Google Spreadsheet

You'll need to combine the lat/lon columns into a concatenated field and then hand them to the DISTANCE function which will return meters between the 2

LinkOps
  • 331
  • 3
  • 14
1

Formula of distance between two points for Google Sheet

  =  12742 * ASIN(SQRT(0.5 - COS((lat2 - lat1) * 0.017453292519943295 )/2 + COS(lat1 * 0.017453292519943295) * COS(lat2 * 0.017453292519943295) *   (1 - COS((lon2 - lon1) * 0.017453292519943295))/2))

where 1 point coordinates - lat1 lon1, 2 point - lat2 lon2.

Based on discussion Calculate distance between two latitude-longitude points? (Haversine formula)

Sergey Pavlov
  • 29
  • 3
0

Here's an example that I made comparing geocode accuracy between many different online providers. Built into the spreadsheet are numerous calculations to determine the delta (distance) between two geocode points.

https://docs.google.com/spreadsheets/d/1I2rEVX2CN8AqkhpzUTuNwNvJy8il1exccrsd4OGwDCU/edit#gid=0

Jeffrey
  • 502
  • 2
  • 10