Euclidean distance matrix in excel

Question

I'm in desperate need of some Excel related help.

Let's say I have 4 different segments. Each segment has 7 numeric attributes.

A(200;43;23;1.5;16;50000;14)  
B(250;41;23;2.1;19;70000;13)  
C(179;37;25;3.4;20;15000;12)  
D(402;49;19;1.9;25;99000;11)

Let's say each segment has it's own row(1-4) and each attribute has its own column(A-G). I'm calculating the euclidean distance between each separate row by using this equation:

=SQRT(SUMPRODUCT((A1:G1-B2:G2)^2))

How could I edit this equation so that if I created a matrix like this:

\ A B C D  
A  
B  
C  
D  

There should be 0's on diagonals and distance between segments in the cells where given segments cross? Every time I use '$' to fix a row or column I get incorrect results and I have ran out of ideas.
I hope I expressed my problem so that everyone could understand.

Answer 1

Not sure exactly how the equation you posted is even working; SUMPRODUCT needs ranges of values separated by commas. For the euclidean distance between two points in one dimension, the formula should look like this (please let me know if I misunderstood your data though):

=SQRT((SUM(A1:G1)-SUM(A2:G2))^2)

As for the matrix, I used INDIRECT to make it work. However, to make the formula simplest, I had to name the rows and columns of the matrix 1 to 4. Here's an image of the final result:

The formula you see in the screenshot in the first cell of the matrix (L2) is:

=SQRT((SUM(INDIRECT("A"&$K2&":G"&$K2))-SUM(INDIRECT("A"&L$1&":G"&L$1)))^2)

After putting that formula in L2, I just drag-copied down to L5, then drag-copied the entire matrix column across to O.

Basically what this formula is doing is using the matrix's row and column names to create an indirect call to each segment's array. (So basically for it to work, the titles of your matrix's columns and rows need to match the rows the segment data is in.)