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.

 

[Peter_SSs]

type88

It is obviously a bit hard to know what is going wrong without being able to see your sheet. However, a couple of things to consider:

1. My formulas are based on the grid starting at cell A1 as shown in your screen shot. If the top left corner is elsewhere, adjustments to the formula would be needed.

2. Your original post said that you had 136 cities. Your last post said that you have a problem with column FT (and others). I think that if you started at column A and have got to FT then there is more than 136 cities. There is nothing wrong with that of course, but it is making me wonder if the sheet structure is different to my example and therefore the formulas may need adjustment.

3. Can you confirm that your city list in GA starts at row2 and has no gaps in the list, including in hidden rows? (Gaps in the city list is one way to produce the #N/A error)

tactps
1. If my previous post is producing the sort of results required by type88, and from the subsequent posts I think that it is, then the idea of adding the cell to the left and the cell below does not work. eg In my post, F3=90 but E3+F4 would give 120.

2. As you stated, the formula would also have to be modified below the diagonal.

type88 & tactps
OK, using tactps' idea of a simple addition each time and type88's request for a single formula for the whole grid, this may be better.

This time I have put the intermediate distances between the cities across the bottom (rows 8, 9).

The formula in B2 (copied across and down the whole grid):

=IF(ROW()=COLUMN(),"",IF(ROW()<COLUMN(),SUM(A2,B$9),SUM(C2,C$9)))

Decimals don't seem to be a problem.

Mr Excel.xls
	A	B	C	D	E	F
1		City1	City2	City3	City4	City5
2	City1		4.0	8.5	18.5	24.0
3	City2	4.0		4.5	14.5	20.0
4	City3	8.5	4.5		10.0	15.5
5	City4	18.5	14.5	10.0		5.5
6	City5	24.0	20.0	15.5	5.5
7
8		City1	City2	City3	City4	City5
9		0.0	4.0	4.5	10.0	5.5
Distance Chart (2)

 

[type88]

Finished

Peter.

Thank you for all of your help. I finally have it figured out. The fields which turned up NA did so for a variety of reasons. I was just beginning to suspect the "I" column as the culprit when you posted your last comments. Taking a closer look at the fields in that column I discovered that if the name of the city was not identical to its counterpart in row 1 the formula fails.

With that in mind, is there a way to maintain only one list of cities and have the other columns and rows get in line? If I put all of the cities in the "I" column, can they be repurposed in ROW 1? or for that matter COLUMN 1?

I will save your second strategy for a later date.

m.

 

[Peter_SSs]

type88

I think that you really should have a look at the second strategy, as the concept and formulas are much simpler. In this secod strategy the names across row 8 are there only to clarify what the row is for and are not used at all in the formulas. They could be removed and you would just have your list across row 1 and down column A. That would solve the problem of different spellings causing the formulas to fail.

If you insist on continuing with the first strategy, to avoid the spelling problems, in I2 (or GA2 I think for you) put the formula =OFFSET($A$1,0,ROW()-1) and copy it down the column. That way the spelling will be identical to that across row 1 (these are the ones used in the formulas). Another way to get the list the same as row 1 is to select all the city names across row 1 and choose 'Copy', then select cell GA2 and choose Edit|Paste Special...|Transpose|OK