Creating a Distance Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm interested in creating a distance table by the easiest possible way in
Excel 2003. With a list of places going down the sheet, can anyone suggest
how I get a formula to copy these cells into other cells going along the top?
I assume this could be adapted or directly used to duplicate distances as
neccassary. For example

Place1 Place2 Place3...
Place1 0 6 4
Place2 6 0 2
Place3.... 4 2 0

Any ideas appreciated.
 
That's close, but it won't let me paste over the original selection to get
the end result I'm after. I was also wondering if a formula would do it so I
can insert new places as I go, up to a total of 165 places. I'm not planning
on typing that all in, in one go...


Thanks for your quick response before!
 
If the cities start in A2 and go down, put this in B1:

=INDEX($A$2:$A$20,COLUMN(A1))

And copy to the right. Of course $A$20 is variable.
 
That's pretty cool. It certainly copies the place names easily enough.
I guess that for each row I would have to individually change it to
something like
(in column E)
=INDEX($B$2:$B$20,COLUMN(E2)) in row 2
=INDEX($C$2:$C$20,COLUMN(E3)) in row 3 etc etc to get the distances copied
from one side to the other. If I do it manually, that is still 165 changes to
make. Can you think of an easier way of changing the parameters, or would I
be looking at a macro? *Shudder*

Thanks very much,

MKJ
 
=INDEX($A$1:$K$11,MATCH(K$1,$A$1:$A$11,0),MATCH($A2,$A$1:$K$1))

This formula would be put in column K (as an example) and copied to al
cells
above the diagonal 0 line. Then all you would have to do is change th

distances on the bottom half and the top half would be update
automatically.

To clarify what this is doing:

$A$1:$K$11 is the entire range of data
K$1 the K is whatever column you are putting the formula in and 1 i
the row
where your cities are listed across the top.

all other values would have to be adjusted accordingly to reflect you
data.

Is this what you are looking for? It is hard to explain and I fee
like I may have
done a poor job. If you are familiar with excel functions then you ca
probably
play around with it and get it to work
 
Example only....adjust ranges and cities.

You have a table A1:J10

Leave A1 blank.

Say A2:A10 contains city and B1:J1 contains city1

e.g

A2 = Quebec
B1 = Quebec1

A3 = Ottawa
C1 = Ottawa1

A4 = Timmins
D1 = Timmins1

B2:J10 contains mileages.

One method is to select the entire table A1:J10 then choose
Insert>Name>Create, and select top row and left column.

Then use the intersect functionality:

=city city1

In above example =quebec ottawa1


This will return the value of the cell at the intersection of city and city1


Gord Dibben Excel MVP




Hi,

I'm interested in creating a distance table by the easiest possible way in
Excel 2003. With a list of places going down the sheet, can anyone suggest
how I get a formula to copy these cells into other cells going along the top?
I assume this could be adapted or directly used to duplicate distances as
neccassary. For example

Place1 Place2 Place3...
Place1 0 6 4
Place2 6 0 2
Place3.... 4 2 0

Any ideas appreciated.
Click to expand...

Gord Dibben MS Excel MVP
 
Hi,

That's excellent! One formula that can be pasted anywhere in the upper half
and be expected to work. (Obviously modifying for different sized tables.)
Exactly what I was hoping to find. Thank-you very much for your help.

MKJ
 
Hello.

I'm afraid you lost me slightly there, though I did play with the feature to
Insert>Name... That could be useful later on.
mphello found a solution that I'll use, but thanks for your efforts on this
anyway! It's much appreciated.

MKJ
 
Hi,

Im creating a similar table showing the distances between 112 schools in our
borough. As this is a very large table of information, I want to have a
simpler worksheet for colleagues to use without scrolling all-over a large
sheet of figures.
I want to have 2 drop-down lists to make a "from" and "to" selection, then
be presented with the distance taken from the table on the other worksheet,
any ideas how I can do this?

Thanks,
Chris
 
Create a table in another worksheet.

Put the names of the school in A2:A113
and put the names of the school in B1:DH1
Put the distances between schools in that giant table.
(Kind of like the table you see in maps)

Then you can use a couple of cells with data|validation (use A2:A113) as the
source.
See Debra Dalgleish's site to see how to name that range so that it can be used
as the data|validation list:
http://contextures.com/xlDataVal01.html#Name

Now you can use a formula to return the intersection of those two choices.
Time to visit Debra's site once again:
http://contextures.com/xlFunctions03.html
especially example 2 (or 3)

You may want to put this in your formula:

=if(or(a1="",b1=""),"",index(....))

where a1 and b1 hold the two school names.
 
Back
Top