I think I can help you (finally).

Assume the range containing your data is named Test (or replace w/$A$1:$C$3
or the appropriate range reference for your data). Try entering this formula
in cell E1 (change E1 in the formula to whatever cell you actually enter the
formula in). Then copy across and down.

=SUMPRODUCT(INDEX(Test, ,ROW()-ROW($E$1)+1)*INDEX(Test,
,COLUMN()-COLUMN($E$1)+1))

Maybe the formula could be done w/Offset, but the index function can also
return entire rows and columns. Note the space where the row argument should
be for the Index function.

" wrote:

OK, let's try this.

A1:C3 has the data

A B C
1 4 3 6
2 5 2 5
3 6 1 4

What I'm trying to achieve is another nine values in a sqaure array:

4*4+5*5+6*6 4*3+5*2+6*1 4*6+5*5+6*4

3*4+2*5+1*6 3*3+2*2+1*1 3*6+2*5+1*4

6*4+5*5+4*6 6*3+5*2+4*1 6*6+5*5+4*4


Notice the first number in each product comes from i-th column of the
original square matrix, where i denotes the row in the new matrix.

The second number in each product comes from the j-th column of the
original sqaure matrix, where j denotes the column of the new matrix.

Rows and columns are numbered from 1 to 3 (in this simple case).

The end matrix is symmetrical. The element from row 2, column 1 is the
same as the element from row 1, column 2 since they are both equivalent
to the SUMPRODUCT of the frist and second columns of the original
matrix.

Does this help?

Clive