I would like to calculate the distances between postcodes (crow flies would do). Is there a way to do this in Excel. I can list the codes in 2 columns
Replies (14)
Please login or register to join the discussion.
Not easily
If you were to put the longitude and latitude of every postcode in, it could be done. Otherwise, the varying areas postcodes cover, usually based on how dense the properties are, make it a non-starter with just the codes themselves.
Why not just use an online service, like Google Maps? Using that I've just determined it is 273 miles from HMRC Bradford to Companies House Cardiff driving.
Easy - MapPoint
You could import to MS MapPoint from Excel to achieve this and then export back to Excel.
northings and eastings
If you subscribe to the postcode data (as we do) it is relatively easy to resolve the distances in Excel.
We do a lookup of the northings and eastings for each postcode then work from the values given.
This does of course give a straight line distance but over sets of data and areas we have found that a localised percentage can be reliably used for estimation of road distances in the first instance just to sanity check.
Very interesting question
I can see that this situation could crop up if you had to calculate mileages for deliveries or expenses, which suggests that a road distance mechanism would be even more helpful than as the crow flies.
@GazCol - do you think MS MapPoint would be up to this. And if it is, do you think you'd be able to wrangle this? It would make a great import/export and programming tutorial for our Excel Zone!
Hi John,
Yes, MS MapPoint is able to give distances by actual travel, not just how the crow flies. Particularly handy if you've got lots of expenses to file and don't want to be going back and forth from Google Maps to work out the distances.
Where it tends to get used more often is business intelligence; it's a great way to visualise customer, sales and competitor data. Google have really missed a trick in not being able to readily import geo data from excel into Maps/Earth, unless you're working with WGS84 co-ordinates, and what kind of person uses that rather than postcodes?
I have a break between contracts of the Christmas/New Year period so I'll look at putting together a step-by-step guide via YouTube or simple screenshots for Excel Zone.
More options
@PanelMaker The site http://www.freemaptools.com/ has various tools for calculating distances between postcodes and you can also download a CSV file of UK postcodes with their latitude and longitude coordinates from the site. The site also contains links to algorithms that show you how to calculate distances using the coordinates if you want to write your own code. The postcode data you can download comes in 2 flavours: outcodes or full postcodes. The list of outcodes are manageble in size (80KB CSV) and contain the general outcode locations (the part of the postcode before the space). This can be quite large area and may not be fine enough for your calculations, depending on how you are using it. On the other hand the full postcodes list has very fine granularity; it goes down to the final postcode letter which usually represents just a few houses. This means that the files are very large (89MB CSV) and they have too many rows to load into Excel. If you needed an interim granularity (e.g. down to say the first digit or first digit and first letter of the postcode after the space) you could obviously extrapolate this from the full postcode list and build a smaller more manageable list that would fit into Excel. There are a number of ways of achieving this - e.g. suck the list into a SQL or MS-Access database and crunch it using some simple queries to spit out a smaller subset. I haven't used the data for several years but according to the version history it does seem to be updated regularly enough. I'm fairly sure there are other sites that offer the same data since it was made available through the Ordnance Survey OpenData initiative in 2010.
Asked before and answered ....
However, cannot find the post in Aweb search (there's a surprise!)
Have a look at this - http://blog.acmultimedia.co.uk/2008/03/uk-post-code-distance-calculator-...
Technically, with another approach of all postcodes within an area (i.e. Rightmove way then because of the 'great circle' you need to use the Haversine Formula
Also you need to download a postcode file with lat & long - when I last looked it had 23k+ records in it
Just find an online calculator - much simpler
Hi
I remember doing this a while ago to plan my trips around the uk. I will see if I can dig it out as it was quite need programming in VBA from memory...
Worked by having a 'from' and 'to' cell
Thanks
Thanks for the giggle Triggle
SOH CAH TOA
Goodness that took me to my maths o level 25 years ago - Some old Hag cracked all her teeth on apples was the one we used, but I don't know if that's universal for that acromyn?
Very interesting post, thanks to all for responses.
Value Route Planning
A client today was using just Excel to cluster clients for route planning on a value (is the trip worth it) basis, I guess that's another step up.
MapPoint appears to carry stats but I don't see 3D.