• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate distance between Multiple latitude longitude

Harry0

Harry0

Member
I am trying to see which locations are closer in order by route. Going by zip code does not seem to work since they can vary and are not exact, so I am using Latitude and longitude which not working at as it should.

The thing I did kind of worked to find the shortest distance from 2 locations, and then I referenced it to 3 locations in all and averages it out. I sorted it to see if the order is right and I does not seem to be in some ways. Also I used in the formula 3 locations form the city to assume to triangulate it)

I use this site to put the address to see if the triangulations are right but it seems wrong.
Obviously something is not right.

Site I use to check if it is right and to find multiple Latitude and longitude
http://www.mapdevelopers.com/batch_geocode_tool.php
Thanks
 

Attachments

  • distance.xls
    34 KB · Views: 33
:eek:
I can't do that.
Ummm isn't there a way to do it without code and only with excel formulas?
 
That's a slick use of a User Defined Function by Ashish .... You should give it a go Harry0 - you'll get to expand your Excel horizons and its easy to do ...
 
  • Like
Reactions: Hui
From what ashish states in the post "Find Distance between two places", which I have the excel formula attached for that, but I am looking for multiple places.

(formula: =ACOS(COS(RADIANS(90-A2)) *COS(RADIANS(90-A3)) +SIN(RADIANS(90-A2)) *SIN(RADIANS(90-A3)) *COS(RADIANS(B2-B3))) *6371

The error that comes up and will come up is if Location A and Location B are close to equally distant to Location Zero (location A slightly closer which it will put that as first), it will put those places as 1st and 2nd, while Location C will be chosen as the 3rd location despite it is near Location A.

So if it looks like this below, what is the best route to take that is efficient? The up down up or the up and down?
C
A
Zero
B

The codes now state to go from zero to ABC
Instead of going to Zero ACB
let alone an option to select where to go to first to the greater number or lesser to be zero BAC.
Now the trick is for something that is a much longer route in a map with 2 axis.
 
Well, although it appears that you know what you're trying to achieve, it is not apparent to me what your goal is here - I suspect Luke M is similarly confused, although I shouldn't speak on his behalf .... :confused:o_O
 
ok a picture in a way is attached.
Shortest distance within each other not by origin.
See sheet 2
It should be the one on the left not on the right.
Thanks
 

Attachments

  • distance.xls
    40 KB · Views: 16
These 2 images look identical to me. :eek: What is the difference? (other than the fact that one is a bold X)
upload_2014-11-7_9-19-48.png
 
Odd the numbers did not show on your screen. I took a snap shot of it attached.
It should be the order on the left not on the right.
Thanks
 

Attachments

  • Screen Shot 2014-11-07 at 11.30.09 AM.png
    Screen Shot 2014-11-07 at 11.30.09 AM.png
    16.7 KB · Views: 4
I *think* what you're asking for is this ...

Starting at X, find the nearest point from all those presented. Go to that point, then find the nearest point to that one, and so on until all points have been categorized. Have I correctly divined your objective?
 
If David's guess is not correct, I would then guess that goal is to find shortest line/route connecting all points.

Sadly, if I am write, there is no equation that has been found to do this. Funny enough though, the answer is still easily found with a bit of soap and water! :)
http://www.cs4fn.org/optimization/gettingthere.php

Science is cool! Using the surface tension of soap bubbles lets train route designers figure out best way to connect different cities.
 
Hi ,

If you can clearly define the objective , it will be easier to see :

1. If a solution is at all possible

2. If possible , is it feasible using Excel

Are you interested in finding out the nearest point to a given point ? If yes , then I do not understand why you need a third point in these calculations.

Are you interested in traversing a list of points in such an order that results in the shortest route ? If yes , this is a well-known optimization problem known as the travelling salesman problem.

See if the following links help :

http://blog.excelmasterseries.com/2014/05/solving-traveling-salesman-problem-with.html

http://mathematica.stackexchange.com/questions/15985/solving-the-travelling-salesman-problem

Narayan
 
Dave what you say sounds easier said than done. ;)
Soap? lol

So it seems that is a problems... a Traveling sales mans problems. Which gets to be confusing.
Well I was thinking of the existing formula to find the shortest distance and putting a index /match in the middle of it and use a helper to sort it.
(formula: =ACOS(COS(RADIANS(90-A2)) *COS(RADIANS(90-A3)) +SIN(RADIANS(90-A2)) *SIN(RADIANS(90-A3)) *COS(RADIANS(B2-B3))) *6371

Maybe have to think of it all within a graph and play with the numbers to find a pattern.
Thanks anyway
 
You must log in or register to reply here.
Back
Top