shortest distances

  • Thread starter Thread starter nodel oxles
  • Start date Start date
N

nodel oxles

I would like to make a spreadsheet that would calculate the shortest
distance between any two towns and list the route that should be
followed. I presume that it would consist of a database of towns with
distances and names of the next town on each road out of town. I
would then be able to enter the name of a start town and and an
destination town and then it would select the best combination and
list the route with the distances to each town enroute and a total
distance. I have been thinking about using lookup tables but run into
problems finding a way to combine the information. Is this a job that
could be done in Excel and how would I go about it? I realize that
this sort of thing may have already been invented and would like to
see any examples that I may be able to adopt.

Any help or guidance will be appreciated.

Nodel
 
In the olden days, there were books that contained highway maps. The Atlas had
a table in the back that would show distance between towns.

It would have a column of cities down the left hand side and a row of cities
across the top.

You could do the same kind of thing for each road that leaves one town and
enters another. I guess I don't see why you would take the time to enter
anything but the fastest route, though.

So you could just look at that table and find the shortest route.

But I surely wouldn't bother doing this in excel. I don't think I could keep it
up to date and there are better tools available.

I like mapquest for this kind of thing.

http://www.mapquest.com
 
Hi!

And in the UK we have, inter alia, The AA (a motoring organisation fo
example)

http://www.theaa.com

But, seriously, this is a specialised computing area. Excel woul
happily show you pre-constructed routes but it is not going to be abl
to handle the branching logic of a routing system.

I presume if you want to do this that you have some notion of th
number of branch-points (nodes) and the distances between them?

BTW: I think you will find it quite cheap to buy such a routin
program. A lot less than a dollar (or a £) an hour for your time....

Al
 
Nodel,

Do a search under: Traveling Salesman Problem.
It is a well known issue.
The Wall Street Journal even had an article on it recently.

Regards,
Jim Cone
San Francisco, CA
 
...that would calculate the shortest distance ... and list the route..

My opinion is that this would be very difficult in Excel. I would recommend
a dedicated mapping software.
If in the USA, I recommend DeLorme's "Street Atlas USA 2004." It has the
absolute worst user interface ever made for software... but other parts of
the program itself are pretty good. I use it all the time because one can
download the route directly into a hand-held GPS.
You could copy the routing into a note field of some sort of Database.

(I no longer need the GPS interface because the newer GPS's now have
autorouting. However, it's still a nice program to have and offers some
advantages over the online "Mapquest." If needed, you can print out a nice
"Travel Plan.", search for nearby places, etc.)
 
Back
Top