I have a matrix which contains distance for 2 points , if we match them
by using 2 rulers then we get the distance between these points. I
want to create a forumlla that let me get the distance automalicaly
when I wirte the 2 points or you can call it the source and
destination.

for example if I know the distance between many cities so how can I
chose any 2 cities and get the distance between them ?




Thanks for you help in advance


--
memotronic
------------------------------------------------------------------------
memotronic's Profile: http://www.excelforum.com/member.php...o&userid=29840
View this thread: http://www.excelforum.com/showthread...hreadid=495466

http://en.wikipedia.org/wiki/Dijkstra%27s_algorithm

Jerry

memotronic wrote:

I have a matrix which contains distance for 2 points , if we match them
by using 2 rulers then we get the distance between these points. I
want to create a forumlla that let me get the distance automalicaly
when I wirte the 2 points or you can call it the source and
destination.

for example if I know the distance between many cities so how can I
chose any 2 cities and get the distance between them ?




Thanks for you help in advance

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
InsertNameCreate, 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

On Thu, 22 Dec 2005 05:31:55 -0600, memotronic
wrote:


I have a matrix which contains distance for 2 points , if we match them
by using 2 rulers then we get the distance between these points. I
want to create a forumlla that let me get the distance automalicaly
when I wirte the 2 points or you can call it the source and
destination.

for example if I know the distance between many cities so how can I
chose any 2 cities and get the distance between them ?




Thanks for you help in advance

here is a screen capture of microsoft excel.. .hope you get what i
exactly want

see this link

'click here to see the picture'
(http://www.mm.ae/pointofintersect.JPG)

pls help me in finding the correct fuction


--
memotronic
------------------------------------------------------------------------
memotronic's Profile: http://www.excelforum.com/member.php...o&userid=29840
View this thread: http://www.excelforum.com/showthread...hreadid=495466

If your table is as in the link you posted, with cities selected in C14
and C15, the following formula in E14 will give you the distance
between the two inputs:

=VLOOKUP(C14,A2:K11,MATCH(C15,A2:A11,0)+1,1)

Change the cell references if they differ.

I would suggest you do a data validation on cells C14 and C15 using the
list in A$2:A$11, so that the user can only select cities within your
table.

Pete

I tried using lookup fuction but it did not work with me


'distance calculator excel file' (www.mm.ae/distancecalculator.xls)


--
memotronic
------------------------------------------------------------------------
memotronic's Profile: http://www.excelforum.com/member.php...o&userid=29840
View this thread: http://www.excelforum.com/showthread...hreadid=495466

In what way did it not work? Did you get #N/A errors or some other
error, or did you get the wrong values?

I set up a table exactly the same as in your picture, and it worked for
me.

Pete