+ Reply to Thread
Results 1 to 7 of 7

Distance from one location to many others...

  1. #1
    Registered User
    Join Date
    04-26-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2007
    Posts
    2

    Question Distance from one location to many others...

    Hi All

    Long time reader, first time poster!

    I really need some help with a spreadsheet I'm creating... I have a list of building locations which all have latitude/longitude coordinates. At the top I then have a drop box for the home location.

    What I want the sheet to do is, when a home location is chosen in the top section, the distances to all of the other locations calculate in the bottom section.

    Capture.JPG

    I know I can do distance calculations on a 1>1 basis, but not sure how to do it on a 1>Many basis.

    I have attached an example file.

    Thanks in advance for any help...
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    04-26-2013
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: Distance from one location to many others...

    Give this one a go.

    Paste this into cell D5 and copy down to D11.

    =ACOS(COS(RADIANS(90-$B$2)) *COS(RADIANS(90-$B5)) +SIN(RADIANS(90-$B$2)) *SIN(RADIANS(90-$B5)) *COS(RADIANS($C$2-$C5))) *6371

    This gives distance in kilometres and is based on an average Earth radius of 6371km (it's actually an ellipsoid and doesn't have one true radius). Lat Long should be in decimal degrees with negative values indicating West/South.

    Andrew

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Distance from one location to many others...

    whatever your calculation is simply make the part that refers to b2 and c2 absolute the other bit relative
    say distance is calculated by (b2+c2)-(b5+c5)
    make b2 and c2 absolute
    ($b$2+$c$2)-(b5+c5)
    fill down
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    04-26-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Distance from one location to many others...

    That's brilliant! Works a treat!

    Thanks Demesne!

  5. #5
    Registered User
    Join Date
    04-26-2013
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: Distance from one location to many others...

    Not a problem.

    Substitute 6317 for 3958.756 if you want distance in miles

  6. #6
    Registered User
    Join Date
    05-17-2013
    Location
    west babylon, ny
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Distance from one location to many others...

    hello. this is my first question/post. i hope it's okay that i comment here as i believe it is a similar thing. I need to measure 1 to many also. 1 to approx 3700 to be exact. what i've been doing is copying the 1 and pasting all the way down, then see which of the 3700 comes within 150 ft. i need to do that with too many coordinates to do it manually. i guess it's actually MANY to MANY. i'd like to have column a/b with all the coordinates to match, then somehow check them ALL against the 3700 and find the one that is the closest to each. i've attached an brief example.
    Attached Files Attached Files

  7. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Distance from one location to many others...

    Welcome to the Forum.

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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