Matrix calculation

[Guest]

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)

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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