To calculate a table of a function of two variables.

[James Silverton]

In days gone by, I would use programs written in Fortran or C for the
purpose but I recently wanted to calculate a table of distances among
points. Can someone point me to the best way to do this with Excel for
a rather large number of values? In other words, I would finally have
a table each of whose values would be the distance between the row and
column values rather like the distances between cities produced by the
AAA (g). It's not the formula for distance I want but an efficient way
of setting up the calculation.

TIA,

 

[Vasant Nanavati]

Are you saying that you have a database that lists distances between many
city pairs and you want to create a AAA-type table, or do you want Excel to
calculate the distances (I'm not quite sure how you would do that)?

 

[Ragdyer]

XL has an obscure operator called the intersection operator.
It is defined as a "space", and it returns the *intersection* of two ranges.

These ranges can be defined by the colon range operator ), C1:C10,
or by a named range, or a combination of the two.

=C1:C10 A7:E7

Note the space between the ranges.
Will return the intersection of the ranges, namely C7.

If you named the above ranges C1:C10 as "NY",
And A7:E7 as "LA",
Then =LA NY would return the contents of C7, which could be the miles
between.

Is this feature of XL's what you might be pertaining to ?

 

[James Silverton]

Are you saying that you have a database that lists distances between many
city pairs and you want to create a AAA-type table, or do you want Excel to
calculate the distances (I'm not quite sure how you would do that)?

It's the latter I'm afraid!

Jim.

 

[Vasant Nanavati]

Excel has no built-in capability to do that. I've never used MapPoint, but
perhaps that may provide what you're looking for.

There is also a tool called the Great Circle Mapper (http://gc.kls2.com/)
which provides Great Circle distances between worldwide city pairs.

 

[James Silverton]

Excel has no built-in capability to do that. I've never used MapPoint, but
perhaps that may provide what you're looking for.

There is also a tool called the Great Circle Mapper (http://gc.kls2.com/)
which provides Great Circle distances between worldwide city pairs.

The AAA example I gave was just that, an example. The actual distances
I wanted are a little more complicated and involve three dimensional
coordinates and I just wondered if there was a simple and rapid way to
get them using Excel. The creation of a table of values of a function
could have uses other than simple distances. As I said, I know how to
do this is in real programming languages and I could probably quickly
learn enough Visual Basic to do the job but it's hardly worthwhile for
a one-off calculation. It's an interesting problem though, I think!

 

[James Silverton]

The AAA example I gave was just that, an example. The actual distances
I wanted are a little more complicated and involve three dimensional
coordinates and I just wondered if there was a simple and rapid way to
get them using Excel. The creation of a table of values of a function
could have uses other than simple distances. As I said, I know how to
do this is in real programming languages and I could probably quickly
learn enough Visual Basic to do the job but it's hardly worthwhile for
a one-off calculation. It's an interesting problem though, I think!

To add a thought! I don't know much about what Excel calls "Pivot
Tables" but could they be used to get what I want?

 

[Vasant Nanavati]

To add a thought! I don't know much about what Excel calls "Pivot
Tables" but could they be used to get what I want?

I doubt it.

Do you have a formula that converts pairs of three-dimensional coordinates
into distances? If so, building the table that you want should be quite
trivial. Maybe I'm just not understanding the problem.

 

[James Silverton]

I doubt it.

Do you have a formula that converts pairs of three-dimensional coordinates
into distances? If so, building the table that you want should be quite
trivial. Maybe I'm just not understanding the problem.

There is a table of positions with a name in column 1 (say column A)
then numerical values for x, y and z in columns 2, 3 and 4 (say, B, C
and D). The distance of, say, point 5 from point 6 would be:

SQRT( (B5-B6)^2 + (C5-C6)^2 + (D5-D6)^2 )

Jim.