Subtract all individual elements of two matrix

[costanza99g]

Hi,

I'm trying to subtract all the individual elements of two matrices.

One of the matrices is calculated using this function:

Function CovarianceMatrix(rng As Range) As Variant

Dim i As Integer
Dim j As Integer
Dim colnum As Integer
Dim rownum As Integer
Dim auxCovarianceMatrix() As Variant


colnum = rng.Columns.Count
' covariance matrix
ReDim auxCovarianceMatrix(colnum - 1, colnum - 1)

For i = 1 To colnum
For j = 1 To colnum
auxCovarianceMatrix(i - 1, j - 1) = Application.WorksheetFunction.Covar(rng.Columns(i), rng.Columns(j))
Next j
Next i

CovarianceMatrix = auxCovarianceMatrix
End Function

the other matrix (named avcovariancematrix) is calculated using a very similar formula.

I verified that both of these functions work well in the worksheet.

Now I need to subtract all the individual elements of both matrices (basic matrix operation), but I'm getting errors and I don't understand why.
I created a new function that calls the function I posted above and the other one.

This is the function I'm using:
Function AddMAtrix(rng as range) as variant

Aux1=CovarianceMatrix(rng)
Aux2=AverageCovarianceMatrix(rng)

AddMatrix=Aux1-Aux2

end function

thanks

 

[costanza99g]

By mistake I typed the name of one variable incorrectly:

here is the revised post:
Hi,

I'm trying to subtract all the individual elements of two matrices.

One of the matrices is calculated using this function:

Function CovarianceMatrix(rng As Range) As Variant

Dim i As Integer
Dim j As Integer
Dim colnum As Integer
Dim rownum As Integer
Dim auxCovarianceMatrix() As Variant


colnum = rng.Columns.Count
' covariance matrix
ReDim auxCovarianceMatrix(colnum - 1, colnum - 1)

For i = 1 To colnum
For j = 1 To colnum
auxCovarianceMatrix(i - 1, j - 1) = Application.WorksheetFunction.Covar(rng.Columns(i), rng.Columns(j))
Next j
Next i

CovarianceMatrix = auxCovarianceMatrix
End Function

the other matrix (named averagecovariancematrix) is calculated using a very similar formula.

I verified that both of these functions work well in the worksheet.

Now I need to subtract all the individual elements of both matrices (basic matrix operation), but I'm getting errors and I don't understand why.
I created a new function that calls the function I posted above and the other one.

This is the function I'm using:
Function AddMAtrix(rng as range) as variant

Aux1=CovarianceMatrix(rng)
Aux2=AverageCovarianceMatrix(rng)

AddMatrix=Aux1-Aux2

end function

thanks

 

[Ben McClave]

George,

I think that it would work if the function subtracting the two arrays returned an array as well. I adapted your example function to perform this task and it seems to work for me.

Ben

Function AddMAtrix(rng As Range) As Variant

Dim i As Integer
Dim j As Integer
Dim colnum As Integer
Dim rownum As Integer
Dim AddMtrx() As Variant
Dim Aux1() As Variant
Dim Aux2() As Variant

Aux1 = CovarianceMatrix(rng)
Aux2 = AverageCovarianceMatrix(rng)

colnum = rng.Columns.Count
' covariance matrix
ReDim AddMtrx(colnum - 1, colnum - 1)

For i = 1 To colnum
For j = 1 To colnum
AddMtrx(i - 1, j - 1) = Aux1(i - 1, j - 1) - Aux2(i - 1, j - 1)
Next j
Next i

AddMAtrix = AddMtrx

End Function

 

[George Costanza]

Hello,

I still can't make this function AddMAtrix to work.
Below is all the code I'm using.
The varcovar function is working ok (you can try it, selecting data as a matrix of n rows by m columns. the result is a matrix with m rows and m columns).
But when I pass this function to the AddMatrix function I get #value error and I don't understand why.

-------------------------
Option Explicit

Function AddMAtrix(rng As Range) As Variant

Dim i As Integer
Dim j As Integer
Dim colnum As Integer
Dim rownum As Integer
Dim AddMtrx() As Variant
Dim Aux1() As Variant
Dim Aux2() As Variant

Aux1 = VarCov(rng)
Aux2 = VarCov(rng)

colnum = rng.Columns.Count
' covariance matrix
ReDim AddMtrx(colnum - 1, colnum - 1)

For i = 1 To colnum
For j = 1 To colnum
AddMtrx(i - 1, j - 1) = Aux1(i - 1, j - 1) + Aux2(i - 1, j - 1)
Next j
Next i

AddMAtrix = AddMtrx

End Function

Function VarCov(rng As Range) As Variant

Dim i As Integer
Dim j As Integer
Dim colnum As Integer
Dim matrix() As Double

colnum = rng.Columns.Count
ReDim matrix(colnum - 1, colnum - 1)

For i = 1 To colnum
For j = 1 To colnum
matrix(i - 1, j - 1) = Application.WorksheetFunction.Covar(rng.Columns(i), rng.Columns(j))
Next j
Next i

VarCov = matrix

End Function

 

[Ben McClave]

Well, this is not the most elegant of solutions, but I think that it works.I basically took the guts of the two VarCov functions and added them together. It seems to work OK on my machine. My guess as to why it wasn't working before is that the VarCov functions are returning an array, but when your AddMatrix function calls the VarCov function, it is feeding it a range.There are some articles out there on using arrays in formulas, but the workaround below may work for you. Best of luck,

Ben

Function AddMatrix(rng As Range) As Variant

Dim i As Integer
Dim j As Integer
Dim colnum As Integer
Dim matrix() As Double

colnum = rng.Columns.Count
ReDim matrix(colnum - 1, colnum - 1)

For i = 1 To colnum
For j = 1 To colnum
matrix(i - 1, j - 1) = Application.WorksheetFunction.Covar(rng.Columns(i), rng.Columns(j)) + _
Application.WorksheetFunction.Covar(rng.Columns(i), rng.Columns(j))
Next j
Next i

AddMatrix = matrix

End Function