Excel to calculate distance in miles between post codes

[Alpacino]

Hi all,

Is there anyway of doing this???

 

[VoG]

It can be done but you need to convert the post codes into Eastings and Northings. You would need to obtain a lookup table to do this. Then you can calculate distances using Pythagoras' Theorem like this http://www.mrexcel.com/forum/showthread.php?t=329669

Edit: the distances in my sheet are in metres.l

 

[energman58]

http://www.freemaptools.com/download-uk-postcode-lat-lng.htm

has a database of approximate lat/long for all the UK high level post codes like SW1 TA2 WA10 etc. I have used this to work out approximate distances. If you can find one of Ordnance Survey grid references it would make the maths a bit simpler - you need to account for the fact that the earth gets smaller and the lines of lattitude and longitude get closer togetehr as you go north.

Not sure how the location of the post code is defined though and some of the post codes in Scotland are HUGE so you may have problems

If you want to get more precise the Ordnance Survey give locations for each full post code here:

https://www.ordnancesurvey.co.uk/oswebsite/products/code-point-open/index.html

But there are 1.7 million of them and the file is 20MB. I dont know of anything inbetween that woudl take you closer than the first but not be as big as the second - it may be out there somewhere!

 

[jdesi]

1. Find data that ties postcode to latitude & longitude

one example at http://www.zipinfo.com/products/z5ll/z5ll.htm - this is about a $40 download, may be some free data if you search
Sample Records
City Name State Zip ... Latitude (deg) Longitude (deg)
Aurora CO 80010 ... 39.7403 -104.8598
Denver CO 80203 ... 39.7318 -104.9825
etc


2. Figure out the distance between the two points
- look up how to map lat/long data to actual distance* and **
- fiddle around / backcheck against actual published distance data (driving distances) for a few known pairs

* 15 minutes on Google will help
http://www.google.com/search?q=calculate+distance+from+latitude+and+longitude

**one good reference - much more detailed / accurate - need to translate to Excel
http://www.movable-type.co.uk/scripts/latlong.html

 

[energman58]

If you have lat/long this formula gives a good approximation to the distance between two points on a sphere

d = acos(sin(lat<SUB style="POSITION: relative; LINE-HEIGHT: 0; BOTTOM: -0.25em; FONT-SIZE: 0.75em; VERTICAL-ALIGN: 0px; TOP: 0.8ex">1</SUB>).sin(lat<SUB style="POSITION: relative; LINE-HEIGHT: 0; BOTTOM: -0.25em; FONT-SIZE: 0.75em; VERTICAL-ALIGN: 0px; TOP: 0.8ex">2</SUB>)+cos(lat<SUB style="POSITION: relative; LINE-HEIGHT: 0; BOTTOM: -0.25em; FONT-SIZE: 0.75em; VERTICAL-ALIGN: 0px; TOP: 0.8ex">1</SUB>).cos(lat<SUB style="POSITION: relative; LINE-HEIGHT: 0; BOTTOM: -0.25em; FONT-SIZE: 0.75em; VERTICAL-ALIGN: 0px; TOP: 0.8ex">2</SUB>).cos(long<SUB style="POSITION: relative; LINE-HEIGHT: 0; BOTTOM: -0.25em; FONT-SIZE: 0.75em; VERTICAL-ALIGN: 0px; TOP: 0.8ex">2</SUB>−long<SUB style="POSITION: relative; LINE-HEIGHT: 0; BOTTOM: -0.25em; FONT-SIZE: 0.75em; VERTICAL-ALIGN: 0px; TOP: 0.8ex">1</SUB>)).R

Where R is the radius (typically 6371km for the Earth)

Dont forget you will have E and W longitudes in the UK so you need to take that into account by converting them into angles that mean something in the above formula