Distance matrix calculations

[type88]

I hope I can describe this one correctly.
I am trying to creat a matrix of distances like the ones you find on road maps. They have a sequential list of cities (or highway exits) running along the top row and the left column. You run your finger from the starting point (on the left) to the appropriate column where you find the distance.

I have set up the cities along my desired chart (there are 136 of them) and have filled in the distances from one city to the next which is a diagonal line running from the top left+1 column to the bottom right. The reasoning behind that was that if I determine the distance between any two consecutive cities to be in error I can change it and have the rest of the fields fill in automatically. That is where this group comes in.

Is there a formula that I can use to automatically fill in the rest of the distances, and if so is it possible to use the same formula in every box. There are of course multiple ways to calculate the distance, but the one i had in mind was to sum the diagonals between a given point. For example, the value of H3 would be a calculation of D3 + E4 + F5 + G6 + H7. (where H is the 7th city along the line).

All help is deeply appreciated.

m.

 

[tactps]

Without seeing the sheet, I can only assume that the formula you gave is comparable to the grid:

Dim cell As Range
Range("h3:h6").Select
For Each cell In Selection
If cell.Column <> cell.Row Then
cell.Value = 0
counter = 0
Do Until counter = cell.Column
On Error GoTo NextOne:
cell.Formula = cell.Value + cell.Offset(cell.Column() - 4 - counter, -counter).Value
counter = counter + 1
Loop
NextOne:
End If
Next cell
End Sub

 

[type88]

One step at a time

Perhaps I am in a bit over my head. I can follow along (mostly) with what you wrote but I have no idea where to begin implementing or triggering it. I took a screenshot to give you an idea what the chart looks like without any calculations.

I hope that this makes things a bit more clear. Can you help a novice make the code above work?

Ignore the "567" that crept into the grid.


m.

 

[tactps]

Wouldn't each one just be the sum of the one to the left and the one below?

So in cell D2m you would input
=c2+d3

Then copy and paste this into all blank cells (similar thing below the line).

This makes St Jean to Trinidad 372 km (or miles I guess).

 

[Peter_SSs]

type88

Here is one possible approach, given that you have a large table (136 cities) and you want the same formula in all cells.

1. Make a vertical table of the distances rather than a diagonal one. I have done this in columns I & J in my example below (Note: starting at row 2). The distance between City1 & City2 is 50, between City2 & City3 is 40 etc. These columns can be off to the right of your table and could be hidden later if required.

2. In cell B2 put this formula:

=IF(ROW()=COLUMN(),"",IF(ROW()<COLUMN(),SUM(OFFSET($I$1,ROW(),1,MATCH(B$1,$I:$I,0)-ROW())),OFFSET($A$1,COLUMN()-1,ROW()-1)))

3. This formula can be dragged across and down the whole table.

4. If you want B2, C3 etc coloured, you could do this manually after the formulas have benn dragged, or you could select your whole table and then Format|Conditional Formatting...|Formula Is| =ROW()=COLUMN() and set the colour.

Mr Excel.xls
	A	B	C	D	E	F	G	H	I	J
1		City1	City2	City3	City4	City5
2	City1		50	90	120	140			City1
3	City2	50		40	70	90			City2	50
4	City3	90	40		30	50			City3	40
5	City4	120	70	30		20			City4	30
6	City5	140	90	50	20				City5	20
Distance Chart

 

[tusharm]

In general, the distance between city1 and city3 is not the sum of distances between the pairs (city1, city2) and (city2, city3). Just think of any three cities that do not lie in a straight line. 3 such major US cities would be NYC, Chicago, and Atlanta. The distance between Atlanta and Chicago is not the sum of the distances between Chicago & NYC and NYC & Atlanta.

{snip}
There are of course multiple ways to calculate the distance, but the one i had in mind was to sum the diagonals between a given point. For example, the value of H3 would be a calculation of D3 + E4 + F5 + G6 + H7. (where H is the 7th city along the line).

All help is deeply appreciated.

m.

 

[type88]

Very neary there

Thanks a bunch, I am almost there.
Tactsps, I agree with your thought that each distance would just be the sum of the one to the left and the one below, but did not have the skills to put together the offset required to do so. I thought it could be done but had no place to start. It may still be the best way given some of the drawbacks in Peter's strategy. see below.

Peter: I have adopted your approach with 76% success. The list off the chart done vertically is ultimately the best solution as it allows me to change distances later with ease. Additionally it can be imported to my design program more easily.
The only remaining questions I have, and they will illustrate my complete ineptitude with excel, is that the formula comes back with an #N/A for any portion whose distance is not a whole number. The second problem, and the more pressing one, is that if you change the distance between two cities, the distances are only updated for one row. In your example, if you change the distance between city1 and city2 to something noticeably larger the distance between city2 and city5 does not change. Am I doing something wrong, or is the thinking flawed?

Tushar: You are positively correct with the notion that the distance between two cities is not the sum of the parts between the cities. Within this application however there is now way to get from point1 to point3 without going through point2. It is a trail, covered on foot, which makes me cringe at the notion of walking from NYC and Atlanta. Yikes.

Much obliged.
m.

 

[type88]

Ignore that last one.

For those of you laughing at my stupidity, I encourage you to enjoy it.

Please disregard the foolish comment I made regarding peter's strategy, particularly the one where changing the distance between city1 and city2 does not affect the distance betwen city2 and city5. Of course it doesn't.

It was a long graveyard shift and I feel like a barrel of smashed ***. Now, how about the N/A decimal problem.

m.

 

[tactps]

Tursham,

I absolutely agree that the distance between cities 1 and 3 is not the same as the distance between 1 and 2, then 2 and 3.
However, without the assumption that you go to each city via the last, you'd need to input every cell anyway (or at least half of them to pick up the other diagonal).

Peter, your offset idea is a good one, but I don't think it achieves much more that the formula I provided (=c2+d3).

Therefore, type88 - problem solved.

Please correct me if I'm wrong.

 

[type88]

One to the Left plus One Below

Tactps,

Your idea will not work. The distance between St. Jean and Trinidad, which you quoted to be about 372 is actually 3+4+4.5+10.5+5.5+12.5.


That being said, the problem I noticed earlier with decimal points is not actually a problem with decimal points. It is something else that I cannot track down. I have executed the code precisely as Peter suggests (except that my list is in GA not I) and I get #N/A in all values for columns D E F I Q U AI AN BD BS CV DD EC FB and FT. All values between these points appears to be accurate.

m.