# Post Codes

1. ## Post Codes

Hi I have a very hard question but im sure someones probably already done this and may have some code or formulas.

I have a sheet in a workbook with 100 locations in it. The places are all matched with a post code. It is the beginning part of the post code only. In another sheet I have a giant lookup table full of postcodes along with their longitude and latitude. The download page was

and it does have an sql file with it but I dont know if its compatible with excel.

What I would like to do is to be able to enter a post code into a new sheet and it calculate the distance from the post code to all the locations. Then i want it to output a list in ascending order by distance.

The website gives out references to some websites but the maths is too complex for me without explanation or example to work with.

This is one of the pages
http://en.wikipedia.org/wiki/Haversine_formula

Any help appreciated with this one guys would be good to get it working and useful for others aswell I think as an example spreadsheet sticky

2. This is really easy see this link
http://www.cpearson.com/excel/latlong.htm
Thanks

3. mods - might be a good sticky!

4. =ACOS(SIN(lat1)*SIN(lat2)+COS(lat1)*COS(lat2)*COS(lon2-lon1))*6371
should do it !!!!!!!!

http://www.movable-type.co.uk/scripts/latlong.html
now how to get that in the format op wants
EDIT

i cant get the formuls to chuck out the correct result
se19 to se20 comes out as 85.37392788 somthings! metres/km ?
but using the same lat lon in another checker it comes out as

Distance between 51.411257N 0.059209W and 51.41781N 0.087765W is
2.1125 km which is correct (roughly)
ah ah have to covert degrees to radians first!
so that now gives us
=ACOS(SIN(B2*D1)*SIN(B3*D1)+COS(B2*D1)*COS(B3*D1)*COS(C3*D1-C2*D1))*6371
0.017453293

wher b2 is lat1 b3 lat 2 c2 long 1 and c3 long 2 d1=0.017453293
which is conversi0n factor degrees to rads and hey presto result is
2.110163197 km which is pretty dam close

5. have you got the list of postcodes you want done?
i'll knock something up if you like
ok i did it anyway enjoy!
blast its too big ok i put it here
http://d01.megashares.com/?d01=8f34cc0
use the free service
better still
http://digitalvault.bt.com/invite/lo...c&r=mg&lang=en
click the blue "postcodes"

6. ignore the above modified version here
http://digitalvault.bt.com/invite/lo...e&r=mg&lang=en
click the blue "postcodes"

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1