Excel to calculate distance in miles between post codes

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
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
 
Last edited:

energman58

Well-known Member
Joined
Oct 25, 2010
Messages
553
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

New Member
Joined
Apr 18, 2002
Messages
13
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

Well-known Member
Joined
Oct 25, 2010
Messages
553
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,090,474
Messages
5,414,754
Members
403,543
Latest member
Phx007

This Week's Hot Topics

Top