Matrix calculation
 

Dear all,

I am trying to calculate a matrix B from matrix A by using the following
formula:
B21 = sum(A1S1-A2S1; A1S2-A2S2;A1S3-A2S3;A1S4-A2S24)
B31 = sum(A1S1-A3S1; A1S2-A3S2;A1S3-A3S3;A1S4-A3S24)
B41 = sum(A1S1-A4S1; A1S2-A4S2;A1S3-A4S3;A1S4-A4S24)
....
B32 = sum(A21S1-A3S1; A2S2-A3S2;A2S3-A3S3;A2S4-A3S24)
B42 = sum(A21S1-A4S1; A2S2-A4S2;A2S3-A4S3;A2S4-A4S24)
....
With
Matrix A:
S1 S2 S3 S4
A1 0.98 0.09 0.10 0.97
A2 0.44 0.62 0.44 0.22
A3 0.48 0.09 0.80 0.92
A4 0.51 0.41 0.62 0.24


Matrix B is symmetric (above and below the diagonal are the same):
1 2 3 4
1 1 - - -
2 B21 1 - -
3 B31 B32 1 -
4 B41 B42 B43 1

The matrix B a have to calculate is quite big (100x100), so if I have to add
in the formulas one by one, this will take quite a long time. Does anyone
knows how to do this in a less labour-intensive way?

Many thanks
Maarten
(I am using Office 2003)

Hi Maarten,

If you put your input matrix into cells A1:IV256 then write into cells A257,
B258, C259, ... your constant 1 (one) and into cells A258, A259:B259,
A260:C260, ... the formula

=SUM(OFFSET($A$1,COLUMN()-1,0,1,256))-SUM(OFFSET($A$1,ROW()-257,0,1,256))

Empty cells won't hurt. You can hide them.

HTH,
Bernd

Hi Bernd

thanks, it works fine! Do you also have an idea how to do the same
calculations, but with the sum of the absolute differences (e.g.
sum(|A1S1-A2S1|; |A1S2-A2S2|;|A1S3-A2S3|;|A1S4-A2S24|))

Greets
Maarten

"Bernd Plumhoff" wrote:

Hi Maarten,

If you put your input matrix into cells A1:IV256 then write into cells A257,
B258, C259, ... your constant 1 (one) and into cells A258, A259:B259,
A260:C260, ... the formula

=SUM(OFFSET($A$1,COLUMN()-1,0,1,256))-SUM(OFFSET($A$1,ROW()-257,0,1,256))

Empty cells won't hurt. You can hide them.

HTH,
Bernd

Hi Maarten,

Enter the UDF (user defined function)
Option Explicit

Function sumabsdiff(r1 As Range, r2 As Range) As Double
Dim i As Long
i = 1
Do While Not IsEmpty(r1.Value2(1, i))
sumabsdiff = sumabsdiff + Abs(r1.Value2(1, i) - r2.Value2(1, i))
i = i + 1
Loop
End Function

(push ALT + F11, insert a module and then paste the code)

Put ones into cells A513, B514, C515, etc.
Enter
=sumabsdiff(OFFSET($A$1,COLUMN()-1,0,1,256),OFFSET($A$1,ROW()-513,0,1,256))
into cells A514, A515:B515, A516:C516, ...

HTH,
Bernd