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

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






On Sunday, September 30, 2012 2:22:23 AM UTC+1, wrote:
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

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

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

-------------------------------------------

thanks in advance

On Monday, October 1, 2012 3:13:04 AM UTC+1, Ben McClave wrote:
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

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