shortest distances

[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

 

[Dave Peterson]

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

 

[AlfD]

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

 

[Jim Cone]

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

 

[Dana DeLouis]

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