+ Reply to Thread
Results 1 to 6 of 6

Post Codes

  1. #1
    Registered User
    Join Date
    08-30-2008
    Location
    London
    Posts
    35

    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

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

    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

    Thanks for your help in advance

  2. #2
    Registered User
    Join Date
    08-30-2008
    Location
    London
    Posts
    35
    This is really easy see this link
    http://www.cpearson.com/excel/latlong.htm
    Thanks

  3. #3
    Registered User
    Join Date
    08-30-2008
    Location
    London
    Posts
    35
    mods - might be a good sticky!

  4. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    =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
    Last edited by martindwilson; 09-11-2008 at 03:53 PM.

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    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"
    Last edited by martindwilson; 09-11-2008 at 06:55 PM.

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    ignore the above modified version here
    http://digitalvault.bt.com/invite/lo...e&r=mg&lang=en
    click the blue "postcodes"

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula in a range to add of letter codes
    By realniceguy5000 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-12-2008, 01:25 PM
  2. Replies: 6
    Last Post: 11-28-2006, 01:08 PM

Bookmarks

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