# Distance from one location to many others...

1. ## 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...

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

Give this one a go.

Paste this into cell D5 and copy down to D11.

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. ## 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

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

That's brilliant! Works a treat!

Thanks Demesne!

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

Not a problem.

Substitute 6317 for 3958.756 if you want distance in miles

6. ## 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.

7. ## 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.

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.

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